Oracle with as （hint inline materialize）

1. 测试数据：

SQL> create table test1 as  select * from dba_objects;Table created.SQL> create table test2 as  select * from user_objects;Table created.SQL> create table test3 as  select * from dba_objects;Table created.

收集统计信息：

BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',                                tabname          => 'TEST1',                                estimate_percent => 100,                                method_opt       => 'for  columns owner size repeat',                                no_invalidate    => FALSE,                                degree           => 4,                                granularity      => 'ALL',                                cascade          => TRUE);  END;   /PL/SQL procedure successfully completed.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;11 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2878150729-------------------------------------------------------------------------------------| Id  | Operation       | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |     | 86999 |      16M|          |  1605   (1)| 00:00:20 ||*  1 |  HASH JOIN            |           | 86999 |      16M|          |  1605   (1)| 00:00:20 ||   2 |   TABLE ACCESS FULL | TEST2 |        13 |  65 |     |         3   (0)| 00:00:01 ||*  3 |   HASH JOIN      |           | 86999 |      16M|  9352K|  1602   (1)| 00:00:20 ||   4 |    TABLE ACCESS FULL| TEST1 | 86997 |  8325K|       |       347   (1)| 00:00:05 ||   5 |    TABLE ACCESS FULL| TEST3 | 86999 |  8326K|       |       347   (1)| 00:00:05 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")   3 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")Statistics----------------------------------------------------------          0  recursive calls          0  db block gets       2491  consistent gets       2484  physical reads          0  redo size       3736  bytes sent via SQL*Net to client        523  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)         11  rows processed         加 materialize hint,强制oracle生成临时表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;11 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3288461629----------------------------------------------------------------------------------------------------------------| Id  | Operation              | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |                            |   582M|   165G|       |  3963    (40)| 00:00:48 ||   1 |  TEMP TABLE TRANSFORMATION |                              |       |       |       |        |              ||   2 |   LOAD AS SELECT      | SYS_TEMP_0FD9D661A_155646 |       |       |       |          |              ||*  3 |    HASH JOIN               |                            | 86997 |  8750K|       |   351  (1)| 00:00:05 ||   4 |     TABLE ACCESS FULL       | TEST2                 |    13 |    65 |       |     3  (0)| 00:00:01 ||   5 |     TABLE ACCESS FULL       | TEST1                 | 86997 |  8325K|       |   347  (1)| 00:00:05 ||*  6 |   HASH JOIN             |                            |   582M|   165G|  9352K|  3613     (44)| 00:00:44 ||   7 |    TABLE ACCESS FULL     | TEST3                 | 86999 |  8326K|       |   347  (1)| 00:00:05 ||   8 |    VIEW               |                            | 86997 |    17M|       |   332        (1)| 00:00:04 ||   9 |     TABLE ACCESS FULL       | SYS_TEMP_0FD9D661A_155646 | 86997 |  8750K|       |   332    (1)| 00:00:04 |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")   6 - access("T"."OBJECT_ID"="T3"."OBJECT_ID")Statistics----------------------------------------------------------         55  recursive calls          8  db block gets       2525  consistent gets       2485  physical reads        656  redo size       3736  bytes sent via SQL*Net to client        523  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          3  sorts (memory)          0  sorts (disk)         11  rows processed