查询转换——子查询解嵌套（2）！
来源：互联网 发布：知之者好之者不如 编辑：程序博客网 时间：2024/06/03 11:03
子查询解嵌套与视图合并的相似之处在于子查询也是通过一个单独的查询块来表示的。可合并的视图与可以解嵌套的子查询之间的主要区别在于它们的位置是不同的：子查询位于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 20SQL> 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(selectdeptno 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 20SQL> 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 wheree.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 20SQL> 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行。
- 查询转换——子查询解嵌套（2）！
- 查询转换——子查询解嵌套（2）！
- 查询转换--子查询解嵌套
- MySQL基础（4）——子查询（嵌套查询）、联结表、组合查询
- T-SQL查询——嵌套子查询
- 2.8 子查询解嵌套
- MySQL嵌套查询（子查询）
- 更新（嵌套子查询）
- MySQL-子查询（嵌套）
- 关于sql server嵌套查询—子查询及相关子查询两个例题对照
- oracle子查询嵌套查询
- sql嵌套子查询
- SQL嵌套子查询
- SQL嵌套子查询
- 七、嵌套子查询
- 3.8嵌套子查询
- hive嵌套子查询
- 52.Oracle数据库SQL开发之 子查询——编写嵌套子查询
- 关于学习的一点看法
- 使用vs2010用fread读取结构体内存自动对齐
- ubutun下双屏显示切换
- Directx跟opengl有什么不同
- OpenGL ES 2.0 iPhone 开发指引
- 查询转换——子查询解嵌套（2）！
- vgg Computer Vision Lectures Michaelmas 2009
- 安装infobright
- SQL Server高级参数设置
- 通过MySQL C API界面（mysql自己提供的API方式访问mysql数据库）
- ucs2 和 utf16
- RelativeLayout常用属性介绍
- gson.jar 在android种的使用
- 列数不同数组的一种遍历
