oracle的查询转换之子查询展开

参考链接：https://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_1
             https://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_2
 Subquery unnesting is an optimization that converts a subquery into a join in the outer query and allows the optimizer to consider subquery table(s) during access path, join method, and join order selection. Unnesting either merges the subquery into the body of the outer query or turns it into an inline view. Without unnesting, the subquery is evaluated multiple times, for each row of the outer table, and thus many efficient access paths and join methods cannot be considered. 
==>子查询展开是将子查询转换为它自身和外部查询之间的等价表连接。 这种优化器处理的方式要么将子查询拆开和外部查询表做表连接，要么将子查询转换为内嵌视图再和外部查询做表连接。  如果不做子查询展开，外部查询结果集中的每一条记录，子查询都会作为独立单元被执行一次。

 A WHERE clause subquery belongs to one of the following types: SINGLE-ROW, EXISTS, NOT EXISTS, ANY, or ALL. A single-row subquery must return at most one row, whereas the other types of subquery can return zero or more rows. ANY and ALL subqueries are used with relational comparison operators: =, >,>=, <, <=, and <>. In SQL, the set operator IN is used as a shorthand for =ANY and the set operator NOT IN is used as a shorthand for <>ALL. 
==>where条件语句后的子查询满足如下条件之一，就可能做子查询展开。
SINGLE-ROW（=, >,>=, <, <=, and <>）
EXISTS
NOT EXISTS
IN
NOT IN
ANY
ALL
关于IN/EXISTS,NOT IN/NOT EXISTS 可参看半连接和连接的文章
http://blog.itpub.net/25923810/viewspace-2130719/
http://blog.itpub.net/25923810/viewspace-2130979/

注：这里的代码清单里所涉及的表是HR Sample Schema的表。

代码清单1:子查询未做转换

1. SQL> select count(DEPARTMENT_ID)
   
2.   2 from DEPARTMENTS
   
3.   3 where DEPARTMENT_ID in (select /*+ no_unnest */ DEPARTMENT_ID from EMPLOYEES);
   
4. 
   
5. COUNT(DEPARTMENT_ID)
   
6. --------------------
   
7.                   11

Execution Plan
----------------------------------------------------------
Plan hash value: 2957648803
----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |     4 |    15   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                   |     1 |     4 |            |          |
|*  2 |   INDEX FULL SCAN  | DEPT_ID_PK        |     1 |     4 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| EMP_DEPARTMENT_IX |     2 |     6 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMPLOYEES" "EMPLOYEES"
              WHERE "DEPARTMENT_ID"=:B1))
   3 - access("DEPARTMENT_ID"=:B1)
==>上述内容中可以看到，代码走的是filter类型的执行计划，即不能做子查询展开的子查询通常在执行计划的最后一步才被执行。

代码清单2:子查询中表和外部查询表做连接

1. SQL> select count(DEPARTMENT_ID)
   
2.   2 from DEPARTMENTS
   
3.   3 where DEPARTMENT_ID in (select DEPARTMENT_ID from EMPLOYEES);
   
4. 
   
5. COUNT(DEPARTMENT_ID)
   
6. --------------------
   
7.                   11

Execution Plan
----------------------------------------------------------
Plan hash value: 1973601309
----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                   |     1 |     7 |            |          |
|   2 |   NESTED LOOPS SEMI|                   |    10 |    70 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN | DEPT_ID_PK        |    27 |   108 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
==>上述内容中，可以看到EMPLOYEES表和DEPARTMENTS表做了半连接，即子查询是展开的。
   
代码清单3：将子查询作为内存视图再和外部表进行连接

1. SQL> select count(DEPARTMENT_ID)
   
2.   2 from DEPARTMENTS
   
3.   3 where DEPARTMENT_ID in (select DEPARTMENT_ID from EMPLOYEES union select DEPARTMENT_ID from EMPLOYEES);
   
4. COUNT(DEPARTMENT_ID)
   
5. --------------------
   
6.                   11

Execution Plan
----------------------------------------------------------
Plan hash value: 1661077596
-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |    17 |     8  (25)| 00:00:01 |
|   1 |  SORT AGGREGATE        |            |     1 |    17 |            |          |
|   2 |   NESTED LOOPS         |            |   214 |  3638 |     8  (25)| 00:00:01 |
|   3 |    VIEW                | VW_NSO_1   |   214 |  2782 |     8  (25)| 00:00:01 |
|   4 |     SORT UNIQUE        |            |   214 |   642 |     8  (63)| 00:00:01 |
|   5 |      UNION-ALL         |            |       |       |            |          |
|   6 |       TABLE ACCESS FULL| EMPLOYEES  |   107 |   321 |     3   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL| EMPLOYEES  |   107 |   321 |     3   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN   | DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
==>上述代码中执行计划步骤3中出现了关键字‘VIWE' Name列为'VW_NSO_1',即这里将(select DEPARTMENT_ID from EMPLOYEES union select DEPARTMENT_ID from EMPLOYEES)转换成了内嵌视图VW_NSO_1。

end!
  
  
  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25923810/viewspace-2131491/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25923810/viewspace-2131491/