with materialize

昨天有人问with中加materialize的问题，我回复了一个示例，今天记录下

SQL> select * from v$version where rownum<=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

DROP TABLE test1 PURGE;

DROP TABLE test2 PURGE;
CREATE TABLE test1 AS SELECT * FROM Dba_Objects;
CREATE TABLE test2 AS SELECT * FROM test1;
SET timing ON
/
WITH a AS (SELECT /*+ materialize */ * FROM test2 WHERE test2.owner = 'SCOTT')

SELECT * FROM test1 WHERE EXISTS(SELECT /*+ no_unnest*/ NULL FROM a WHERE a.object_id = test1.object_id);

--------------------------------------------------------------------------------------------------------
| Id  | Operation		   | Name		       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	   |			       |    32 |  6624 |  5242	 (1)| 00:01:03 |
|   1 |  TEMP TABLE TRANSFORMATION |			       |       |       |	    |	       |
|   2 |   LOAD AS SELECT	   | SYS_TEMP_0FD9D6601_27A4F8 |       |       |	    |	       |
|*  3 |    TABLE ACCESS FULL	   | TEST2		       |    12 |  2484 |   292	 (1)| 00:00:04 |
|*  4 |   FILTER		   |			       |       |       |	    |	       |
|   5 |    TABLE ACCESS FULL	   | TEST1		       | 74412 |    14M|   295	 (2)| 00:00:04 |
|*  6 |    VIEW 		   |			       |    12 |   156 |     2	 (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL	   | SYS_TEMP_0FD9D6601_27A4F8 |    12 |  2484 |     2	 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

/*下面这个跑的慢，我直接掐了，哈哈*/
WITH a AS (SELECT * FROM test2 WHERE test2.owner = 'SCOTT')

SELECT * FROM test1 WHERE EXISTS(SELECT /*+ no_unnest*/ NULL FROM a WHERE a.object_id = test1.object_id);

----------------------------------------------------------------------------
| Id  | Operation	   | Name  | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	   |	32 |  6624 |   681K  (1)| 02:16:15 |
|*  1 |  FILTER 	   |	   |	   |	   |		|	   |
|   2 |   TABLE ACCESS FULL| TEST1 | 74412 |	14M|   295   (2)| 00:00:04 |
|*  3 |   TABLE ACCESS FULL| TEST2 |	 1 |	30 |   293   (2)| 00:00:04 |
----------------------------------------------------------------------------

他提供了隐含参数

alter session set "_with_subquery" = materialize/inline/optimizer;"

这个与hint效果是一样的

SQL> alter session set "_with_subquery" = materialize;

会话已更改。

已用时间:  00: 00: 00.00
SQL> explain plan for WITH a AS (SELECT * FROM test2 WHERE test2.owner = 'SCOTT')
SELECT * FROM test1 WHERE  2   EXISTS(SELECT /*+ no_unnest*/ NULL FROM a WHERE a.object_id = test1.object_id);

已解释。

已用时间:  00: 00: 00.09
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 4192358956

--------------------------------------------------------------------------------------------------------
| Id  | Operation		   | Name		       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	   |			       |    32 |  6624 |  5242	 (1)| 00:01:03 |
|   1 |  TEMP TABLE TRANSFORMATION |			       |       |       |	    |	       |
|   2 |   LOAD AS SELECT	   | SYS_TEMP_0FD9D6602_27A4F8 |       |       |	    |	       |
|*  3 |    TABLE ACCESS FULL	   | TEST2		       |    12 |  2484 |   292	 (1)| 00:00:04 |
|*  4 |   FILTER		   |			       |       |       |	    |	       |
|   5 |    TABLE ACCESS FULL	   | TEST1		       | 74412 |    14M|   295	 (2)| 00:00:04 |
|*  6 |    VIEW 		   |			       |    12 |   156 |     2	 (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL	   | SYS_TEMP_0FD9D6602_27A4F8 |    12 |  2484 |     2	 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TEST2"."OWNER"='SCOTT')
   4 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */
	      "C0" "OWNER","C1" "OBJECT_NAME","C2" "SUBOBJECT_NAME","C3" "OBJECT_ID","C4"
	      "DATA_OBJECT_ID","C5" "OBJECT_TYPE","C6" "CREATED","C7" "LAST_DDL_TIME","C8" "TIMESTAMP","C9"
	      "STATUS","C10" "TEMPORARY","C11" "GENERATED","C12" "SECONDARY","C13" "NAMESPACE","C14"
	      "EDITION_NAME" FROM "SYS"."SYS_TEMP_0FD9D6602_27A4F8" "T1") "A" WHERE "A"."OBJECT_ID"=:B1))
   6 - filter("A"."OBJECT_ID"=:B1)

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择29行。