当我们使用with的时候，oracle可能会把with里面的结果转换为临时表，这是只是可能，因为CBO会判断。 inline是不转换成临时表，materialize是强制转换成临时表。
制造数据
drop table test1 purge;
drop table test2 purge;
drop table test3 purge;
create table test1 as select * from dba_objects;
create table test2 as select * from user_objects;
create table test3 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test1');
exec dbms_stats.gather_table_stats(user,'test2');
exec dbms_stats.gather_table_stats(user,'test3');
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> set autotrace traceonly
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;
已选择1931行。
已用时间: 00: 00: 00.20
执行计划
----------------------------------------------------------
Plan hash value: 1215971386
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1931 | 382K| 409 (2)| 00:00:06 |
|* 1 | HASH JOIN | | 1931 | 382K| 409 (2)| 00:00:06 |
|* 2 | HASH JOIN | | 1932 | 196K| 210 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| TEST2 | 1934 | 9670 | 10 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST1 | 71347 | 6897K| 199 (2)| 00:00:03 |
| 5 | TABLE ACCESS FULL | TEST3 | 71349 | 6897K| 199 (2)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1173 consistent gets
0 physical reads
0 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
使用hint inline
SQL> with t as(select /*+inline*/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: 1215971386
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1931 | 382K| 409 (2)| 00:00:06 |
|* 1 | HASH JOIN | | 1931 | 382K| 409 (2)| 00:00:06 |
|* 2 | HASH JOIN | | 1932 | 196K| 210 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| TEST2 | 1934 | 9670 | 10 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST1 | 71347 | 6897K| 199 (2)| 00:00:03 |
| 5 | TABLE ACCESS FULL | TEST3 | 71349 | 6897K| 199 (2)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")
2 - access("T1"."OBJECT