测试数据：

SQL>createtabletest1asselect*fromdba_objects;Tablecreated.SQL>createtabletest2asselect*fromuser_objects;Tablecreated.SQL>createtabletest3asselect*fromdba_objects;Tablecreated.

收集统计信息：

BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'TEST1',estimate_percent=>100,method_opt=>'forcolumnsownersizerepeat',no_invalidate=>FALSE,degree=>4,granularity=>'ALL',cascade=>TRUE);END;/PL/SQLproceduresuccessfullycompleted.SQL>withtas(selectt1.*fromtest1t1,test2t2wheret1.object_id=t2.object_id)select*fromt,test3t3wheret.object_id=t3.object_id;11rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:2878150729-------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|-------------------------------------------------------------------------------------|0|SELECTSTATEMENT||86999|16M||1605(1)|00:00:20||*1|HASHJOIN||86999|16M||1605(1)|00:00:20||2|TABLEACCESSFULL|TEST2|13|65||3(0)|00:00:01||*3|HASHJOIN||86999|16M|9352K|1602(1)|00:00:20||4|TABLEACCESSFULL|TEST1|86997|8325K||347(1)|00:00:05||5|TABLEACCESSFULL|TEST3|86999|8326K||347(1)|00:00:05|-------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")3-access("T1"."OBJECT_ID"="T3"."OBJECT_ID")Statistics----------------------------------------------------------0recursivecalls0dbblockgets2491consistentgets2484physicalreads0redosize3736bytessentviaSQL*Nettoclient523bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)11rowsprocessed加materializehint,强制oracle生成临时表SQL>withtas(select/*+materialize*/t1.*fromtest1t1,test2t2wheret1.object_id=t2.object_id)select*fromt,test3t3wheret.object_id=t3.object_id;11rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3288461629----------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|----------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||582M|165G||3963(40)|00:00:48||1|TEMPTABLETRANSFORMATION||||||||2|LOADASSELECT|SYS_TEMP_0FD9D661A_155646|||||||*3|HASHJOIN||86997|8750K||351(1)|00:00:05||4|TABLEACCESSFULL|TEST2|13|65||3(0)|00:00:01||5|TABLEACCESSFULL|TEST1|86997|8325K||347(1)|00:00:05||*6|HASHJOIN||582M|165G|9352K|3613(44)|00:00:44||7|TABLEACCESSFULL|TEST3|86999|8326K||347(1)|00:00:05||8|VIEW||86997|17M||332(1)|00:00:04||9|TABLEACCESSFULL|SYS_TEMP_0FD9D661A_155646|86997|8750K||332(1)|00:00:04|----------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------3-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")6-access("T"."OBJECT_ID"="T3"."OBJECT_ID")Statistics----------------------------------------------------------55recursivecalls8dbblockgets2525consistentgets2485physicalreads656redosize3736bytessentviaSQL*Nettoclient523bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient3sorts(memory)0sorts(disk)11rowsprocessed