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.00SQL> 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.09SQL> 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行。