oracle hint inline materialize

喜欢ヅ旅行 2022-08-06 02:29 220阅读 0赞

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