22.对IN做子查询展开或视图合并

1.什么是对IN做子查询展开或视图合并?

对IN 做子查询展开或视图合并是针对IN后面是子查询的另一种处理方法，它是指优化器对目标
SQL的IN后面的子查询做子查询展开，或者既做子查询展开，又做视图合并。

能对IN做子查询展开/视图合并，需要满足如下条件:
(1)目标SQL的IN后面是子查询而不是常量集合。
(2)Oracle能对目标SQL的IN后面的子查询做子查询展开。

能做子查询展开分为如下情形:
(1)IN后面的子查询不包含视图，Oracle对其做了子查询展开。
(2)IN后面的子查询包含视图，但由于该视图不能做视图合并，所以Oracle对其做了子查询展开。
(3)IN后面的子查询包含视图，但由于该视图可以做视图合并，所以Oracle即对其做了子查询展开，又对其
做了视图合并。

2.对IN做子查询展开或视图合并的例子。

(1)子查看展开 
create view v_test02 as select * from test02 where rownum<20; 

--有rownum,Oracle不能对其做视图合并。出现V_TEST02 表示没有做视图合并。
--但是做了子查询展开。
select t1.id,t1.name from test01 t1 where t1.id in(select t2.id from v_test02 t2 );
Execution Plan
----------------------------------------------------------
Plan hash value: 3342281848
-----------------------------------------------------------------------------------------
| Id  | Operation		     | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		|     1 |    34 |     6  (17)| 00:00:01 |
|   1 |  NESTED LOOPS		     |		|     1 |    34 |     6  (17)| 00:00:01 |
|   2 |   NESTED LOOPS		     |		|     1 |    34 |     6  (17)| 00:00:01 |
|   3 |    VIEW 		     | VW_NSO_1 |    19 |   247 |     2   (0)| 00:00:01 |
|   4 |     HASH UNIQUE 	     |		|     1 |   247 |	     |		|
|   5 |      VIEW		     | V_TEST02 |    19 |   247 |     2   (0)| 00:00:01 |
|*  6 |       COUNT STOPKEY	     |		|	|	|	     |		|
|   7 |        TABLE ACCESS FULL     | TEST02	|   914K|    11M|     2   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN	     | IDX_ID	|     1 |	|     2   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID| TEST01	|     1 |    21 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(ROWNUM<20)
   8 - access("T1"."ID"="ID")
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
	 33  recursive calls
	  0  db block gets
	545  consistent gets
	  0  physical reads
	  0  redo size
       1159  bytes sent via SQL*Net to client
	530  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 19  rows processed
(2)不做子查看展开
出现FILTER,说明走的是IN-LIST-FILTER;

select t1.id,t1.name from test01 t1 where t1.id in(select /*+no_unnest*/t2.id from v_test02 t2 );
Execution Plan
----------------------------------------------------------
Plan hash value: 1114231498
---------------------------------------------------------------------------------
| Id  | Operation	     | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		|     1 |    21 |  2469K (20)| 08:13:56 |
|*  1 |  FILTER 	     |		|	|	|	     |		|
|   2 |   TABLE ACCESS FULL  | TEST01	|  1000K|    20M|  1097   (1)| 00:00:14 |
|*  3 |   VIEW		     | V_TEST02 |    19 |   247 |     2   (0)| 00:00:01 |
|*  4 |    COUNT STOPKEY     |		|	|	|	     |		|
|   5 |     TABLE ACCESS FULL| TEST02	|   914K|    11M|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM  (SELECT "ID" "ID"
	      FROM TEST."TEST02" "TEST02" WHERE ROWNUM<20) "T2" WHERE "T2"."ID"=:B1))
   3 - filter("T2"."ID"=:B1)
   4 - filter(ROWNUM<20)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
	 14  recursive calls
	  0  db block gets
    4004139  consistent gets
	  0  physical reads
	  0  redo size
       1159  bytes sent via SQL*Net to client
	530  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 19  rows processed

(3)去掉ROWNUM让它走视图合并。
create or replace view v_test02 as select * from test02 ; 
select t1.id,t1.name from test01 t1 where t1.id in(select t2.id from v_test02 t2 );

--没有出现视图，说明走了视图合并的执行计划。

Execution Plan
----------------------------------------------------------
Plan hash value: 735594822
---------------------------------------------------------------------------------------
| Id  | Operation	     | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	      |   914K|    29M|       |  4569	(1)| 00:00:55 |
|*  1 |  HASH JOIN RIGHT SEMI|	      |   914K|    29M|    21M|  4569	(1)| 00:00:55 |
|   2 |   TABLE ACCESS FULL  | TEST02 |   914K|    11M|       |   822	(1)| 00:00:10 |
|   3 |   TABLE ACCESS FULL  | TEST01 |  1000K|    20M|       |  1096	(1)| 00:00:14 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="ID")
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
	 12  recursive calls
	  0  db block gets
      73734  consistent gets
	  0  physical reads
	  0  redo size
   34158163  bytes sent via SQL*Net to client
     733845  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
    1000000  rows processed
	
--子查询展开 
UNNEST(@"SEL$335DD26A"):说明走了子查询展开的执行计划。
MERGE(@"SEL$3"):说明做了视图合并。


select t1.id,t1.name from test01 t1 where t1.id in(select t2.id from v_test02 t2 );
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	2caprd5fsp7yr, child number 0
-------------------------------------
select t1.id,t1.name from test01 t1 where t1.id in(select t2.id from
v_test02 t2 )

Plan hash value: 735594822
---------------------------------------------------------------------------------------
| Id  | Operation	     | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	      |       |       |       |  4569 (100)|	      |
|*  1 |  HASH JOIN RIGHT SEMI|	      |   914K|    29M|    21M|  4569	(1)| 00:00:55 |
|   2 |   TABLE ACCESS FULL  | TEST02 |   914K|    11M|       |   822	(1)| 00:00:10 |
|   3 |   TABLE ACCESS FULL  | TEST01 |  1000K|    20M|       |  1096	(1)| 00:00:14 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$09D7319C
   2 - SEL$09D7319C / TEST02@SEL$3
   3 - SEL$09D7319C / T1@SEL$1
PLAN_TABLE_OUTPUT
-----------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$09D7319C")
      UNNEST(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$335DD26A")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      FULL(@"SEL$09D7319C" "T1"@"SEL$1")
      FULL(@"SEL$09D7319C" "TEST02"@"SEL$3")
      LEADING(@"SEL$09D7319C" "T1"@"SEL$1" "TEST02"@"SEL$3")
      USE_HASH(@"SEL$09D7319C" "TEST02"@"SEL$3")
      SWAP_JOIN_INPUTS(@"SEL$09D7319C" "TEST02"@"SEL$3")
	  END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - (#keys=1) "T1"."ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,30]
   2 - "ID"[NUMBER,22]
   3 - "T1"."ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,30]
Note
-----
   - dynamic sampling used for this statement (level=2)

3.总结 
UNNEST(@"SEL$335DD26A"):说明走了子查询展开的执行计划。
MERGE(@"SEL$3"):说明做了视图合并