测试数据：
SQL> create table test1 as select * from dba_objects;Table created.SQL> create table test2 as select * from user_objects;Table created.SQL> create table test3 as select * from dba_objects;Table created.
收集统计信息：
BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'TEST1', estimate_percent => 100, method_opt => 'for columns owner size repeat', no_invalidate => FALSE, degree => 4, granularity => 'ALL', cascade => TRUE); END; /PL/SQL procedure successfully completed.SQL> with t as(select t1.* from test1 t1,test2 t2 where t1.object_id=t2.object_id) select * from t,test3 t3 where t.object_id=t3.object_id;11 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2878150729-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 86999 | 16M| | 1605 (1)| 00:00:20 ||* 1 | HASH JOIN | | 86999 | 16M| | 1605 (1)| 00:00:20 || 2 | TABLE ACCESS FULL | TEST2 | 13 | 65 | | 3 (0)| 00:00:01 ||* 3 | HASH JOIN | | 86999 | 16M| 9352K| 1602 (1)| 00:00:20 || 4 | TABLE ACCESS FULL| TEST1 | 86997 | 8325K| | 347 (1)| 00:00:05 || 5 | TABLE ACCESS FULL| TEST3 | 86999 | 8326K| | 347 (1)| 00:00:05 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 3 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 2491 consistent gets 2484 physical reads 0 redo size 3736 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed 加 materialize hint,强制oracle生成临时表SQL> with t as(select /*+ materialize */t1.* from test1 t1,test2 t2 where t1.object_id=t2.object_id) select * from t,test3 t3 where t.object_id=t3.object_id;11 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3288461629----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 582M| 165G| | 3963 (40)| 00:00:48 || 1 | TEMP TABLE TRANSFORMATION | | | | | | || 2 | LOAD AS SELECT | SYS_TEMP_0FD9D661A_155646 | | | | | ||* 3 | HASH JOIN | | 86997 | 8750K| | 351 (1)| 00:00:05 || 4 | TABLE ACCESS FULL | TEST2 | 13 | 65 | | 3 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | TEST1 | 86997 | 8325K| | 347 (1)| 00:00:05 ||* 6 | HASH JOIN | | 582M| 165G| 9352K| 3613 (44)| 00:00:44 || 7 | TABLE ACCESS FULL | TEST3 | 86999 | 8326K| | 347 (1)| 00:00:05 || 8 | VIEW | | 86997 | 17M| | 332 (1)| 00:00:04 || 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661A_155646 | 86997 | 8750K| | 332 (1)| 00:00:04 |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 6 - access("T"."OBJECT_ID"="T3"."OBJECT_ID")Statistics---------------------------------------------------------- 55 recursive calls 8 db block gets 2525 consistent gets 2485 physical reads 656 redo size 3736 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 11 rows processed