测试数据：
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 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
