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