oracle hint inline materialize

浏览数：32 / 时间：2015年06月12日

当我们使用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_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 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