查询转换——子查询解嵌套（2）！

子查询解嵌套与视图合并的相似之处在于子查询也是通过一个单独的查询块来表示的。可合并的视图与可以解嵌套的子查询之间的主要区别在于它们的位置是不同的：子查询位于WHERE子句。

/*+ NO_UNNEST */    禁止子查询解嵌套转换。

SQL> select /*+ gather_plan_statistics */ * from emp where deptno in(select deptno from dept where dname = 'RESEARCH');

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c9r1ywanhnt7q, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from emp where deptno in(select
deptno from dept where dname = 'RESEARCH')

Plan hash value: 844388907

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      5 |00:00:00.01 |      11 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |      5 |      5 |00:00:00.01 |      11 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |         |      1 |     14 |      5 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("DNAME"='RESEARCH')

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("DEPTNO"="DEPTNO")
       filter("DEPTNO"="DEPTNO")


已选择25行。

这里的子查询就简单的通过转化为表联结来合并到主查询中。该查询的计划就好像是按如下语句得出来的：

SQL> select /*+ gather_plan_statistics */ e.* from emp e,dept d where e.deptno = d.deptno and dname = 'RESEARCH';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  26zzdpdm0q17j, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ e.* from emp e,dept d where
e.deptno = d.deptno and dname = 'RESEARCH'

Plan hash value: 844388907

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      5 |00:00:00.01 |      11 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |      5 |      5 |00:00:00.01 |      11 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |         |      1 |     14 |      5 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("DNAME"='RESEARCH')

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")


已选择25行。

使用NO_UNNEST提示

SQL> select /*+ gather_plan_statistics */ * from emp where deptno in(select /*+ no_unnest */ deptno from dept where dname = 'RESEARCH');

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fxark931f2dh8, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from emp where deptno in(select
/*+ no_unnest */ deptno from dept where dname = 'RESEARCH')

Plan hash value: 2809975276

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      5 |00:00:00.01 |      14 |
|*  1 |  FILTER                      |         |      1 |        |      5 |00:00:00.01 |      14 |
|   2 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      1 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |      3 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   3 - filter("DNAME"='RESEARCH')

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("DEPTNO"=:B1)


已选择24行。