oracle hint inline materialize，inlinematerialize

oracle hint inline materialize，inlinematerialize

  當我們使用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