子查询解嵌套与视图合并的相似之处在于子查询也是通过一个单独的查询块来表示的。可合并的视图与可以解嵌套的子查询之间的主要区别在于它们的位置是不同的:子查询位于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行。