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 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