使用hint materialize
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;

已选择1931行。
已用时间: 00: 00: 00.21
执行计划
----------------------------------------------------------
Plan hash value: 1492452360
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1925 | 575K| 416 (2)| 00:00:06 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660C_9A3A2AEA | | | | |
|* 3 | HASH JOIN | | 1932 | 196K| 210 (2)| 00:00:03 |
| 4 | TABLE ACCESS FULL | TEST2 | 1934 | 9670 | 10 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST1 | 71347 | 6897K| 199 (2)| 00:00:03 |
|* 6 | HASH JOIN | | 1925 | 575K| 207 (2)| 00:00:03 |
| 7 | VIEW | | 1932 | 390K| 7 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_9A3A2AEA | 1932 | 196K| 7 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | TEST3 | 71349 | 6897K| 199 (2)| 00:00:03 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
6 - access("T"."OBJECT_ID"="T3"."OBJECT_ID")
统计信息
----------------------------------------------------------
394 recursive calls
25 db block gets
1243 consistent gets
18 physical reads
600 redo size
139087 bytes sent via SQL*Net to client
1768 bytes received via SQL*Net from client
130 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1931 rows processed