【DB笔试面试613】在Oracle中，和子查询相关的查询转换有哪些？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了【DB笔试面试613】在Oracle中，和子查询相关的查询转换有哪些？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11120字，纯文字阅读大概需要16分钟。

内容图文

? ? ? ? ? ?题目 ? ? ? ? 部分

在Oracle中，和子查询相关的查询转换有哪些？

? ? ?
? ? ? ? ? ?答案部分 ? ? ? ? ?

（一）子查询推进（Push Subquery）示例

 1LHR@orclasm?>?set?serveroutput?on
 2LHR@orclasm?>?exec?sql_explain('SELECT?/*+?no_push_subq(@lhr_ps)*/?*?FROM?SYS.TAB$?A?WHERE?A.ANALYZETIME?>?(SELECT?/*+qb_name(lhr_ps)*/?MAX(B.ANALYZETIME)?FROM?SYS.IND$?B)','outline');
 3Plan?hash?value:?553156288
 4----------------------------------------------------------------------------
 5|?Id??|?Operation???????????|?Name?|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|
 6----------------------------------------------------------------------------
 7|???0?|?SELECT?STATEMENT????|??????|??3322?|???454K|??1118???(1)|?00:00:14?|
 8|*??1?|??FILTER?????????????|??????|???????|???????|????????????|??????????|
 9|???2?|???TABLE?ACCESS?FULL?|?TAB$?|??3322?|???454K|???559???(1)|?00:00:07?|
10|???3?|???SORT?AGGREGATE????|??????|?????1?|?????7?|????????????|??????????|
11|???4?|????TABLE?ACCESS?FULL|?IND$?|??5545?|?38815?|???559???(1)|?00:00:07?|
12----------------------------------------------------------------------------
13Outline?Data
14-------------
15/*+
16BEGIN_OUTLINE_DATA
17FULL(@"LHR_PS"?"B"@"LHR_PS")
18FULL(@"SEL$1"?"A"@"SEL$1")
19OUTLINE(@"LHR_PS")
20OUTLINE_LEAF(@"SEL$1")
21OUTLINE_LEAF(@"LHR_PS")
22ALL_ROWS
23DB_VERSION('11.2.0.3')
24OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
25IGNORE_OPTIM_EMBEDDED_HINTS
26END_OUTLINE_DATA
27*/
28Predicate?Information?(identified?by?operation?id):
29---------------------------------------------------
301?-?filter("A"."ANALYZETIME">?(SELECT?/*+?NO_PUSH_SUBQ?QB_NAME
31("LHR_PS")?*/?MAX("B"."ANALYZETIME")?FROM?"SYS"."IND$"?"B"))
32
33PL/SQL?procedure?successfully?completed.
34
35LHR@orclasm?>?exec?sql_explain('SELECT?*?FROM?SYS.TAB$?A?WHERE?A.ANALYZETIME?>?(SELECT?MAX(B.ANALYZETIME)?FROM?SYS.IND$?B)','outline');
36Plan?hash?value:?243387038
37----------------------------------------------------------------------------
38|?Id??|?Operation???????????|?Name?|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|
39----------------------------------------------------------------------------
40|???0?|?SELECT?STATEMENT????|??????|???166?|?23240?|??1118???(1)|?00:00:14?|
41|*??1?|??TABLE?ACCESS?FULL??|?TAB$?|???166?|?23240?|???559???(1)|?00:00:07?|
42|???2?|???SORT?AGGREGATE????|??????|?????1?|?????7?|????????????|??????????|
43|???3?|????TABLE?ACCESS?FULL|?IND$?|??5545?|?38815?|???559???(1)|?00:00:07?|
44----------------------------------------------------------------------------
45Outline?Data
46-------------
47/*+
48BEGIN_OUTLINE_DATA
49FULL(@"SEL$2"?"B"@"SEL$2")
50PUSH_SUBQ(@"SEL$2")
51FULL(@"SEL$1"?"A"@"SEL$1")
52OUTLINE_LEAF(@"SEL$1")
53OUTLINE_LEAF(@"SEL$2")
54ALL_ROWS
55DB_VERSION('11.2.0.3')
56OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
57IGNORE_OPTIM_EMBEDDED_HINTS
58END_OUTLINE_DATA
59*/
60Predicate?Information?(identified?by?operation?id):
61---------------------------------------------------
621?-?filter("A"."ANALYZETIME">?(SELECT?MAX("B"."ANALYZETIME")?FROM?"SYS"."IND$"?"B"))
63
64PL/SQL?procedure?successfully?completed.

? ? ?

（二）子查询展开（Subquery Unnesting）

 1--1）IN和EXISTS转换为半连接（SEMI?JOIN）：
 2CREATE?TABLE?EMP_LHR?AS?SELECT?*?FROM?SCOTT.EMP;
 3CREATE?TABLE?DEPT_LHR?AS?SELECT?*?FROM?SCOTT.DEPT;
 4SELECT?*?FROM?EMP_LHR?A?WHERE?EXISTS?(SELECT?1?FROM?DEPT_LHR?B?WHERE?B.DEPTNO=A.DEPTNO);
 5-------------------------------------------------------------------------------
 6|?Id??|?Operation??????????|?Name?????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|
 7-------------------------------------------------------------------------------
 8|???0?|?SELECT?STATEMENT???|??????????|????14?|??1400?|?????7??(15)|?00:00:01?|
 9|*??1?|??HASH?JOIN?SEMI????|??????????|????14?|??1400?|?????7??(15)|?00:00:01?|
10|???2?|???TABLE?ACCESS?FULL|?EMP_LHR??|????14?|??1218?|?????3???(0)|?00:00:01?|
11|???3?|???TABLE?ACCESS?FULL|?DEPT_LHR?|?????4?|????52?|?????3???(0)|?00:00:01?|
12-------------------------------------------------------------------------------
13
14--子查询引用表DEPT，最终转换为两个表的哈希半连接。也就是说，EXISTS子句中的子查询被展开，其中的对象与主查询中的对象直接进行半关联操作。IN的情况类似，如下：
15SELECT?*?FROM?EMP_LHR?A?WHERE?A.DEPTNO?IN?(SELECT?B.DEPTNO?FROM?DEPT_LHR?B);
16-------------------------------------------------------------------------------
17|?Id??|?Operation??????????|?Name?????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|
18-------------------------------------------------------------------------------
19|???0?|?SELECT?STATEMENT???|??????????|????14?|??1400?|?????7??(15)|?00:00:01?|
20|*??1?|??HASH?JOIN?SEMI????|??????????|????14?|??1400?|?????7??(15)|?00:00:01?|
21|???2?|???TABLE?ACCESS?FULL|?EMP_LHR??|????14?|??1218?|?????3???(0)|?00:00:01?|
22|???3?|???TABLE?ACCESS?FULL|?DEPT_LHR?|?????4?|????52?|?????3???(0)|?00:00:01?|
23-------------------------------------------------------------------------------
24
25--2）NOT?IN和NOT?EXISTS转换为反连接（ANTI?JOIN）：
26SELECT?*?FROM?EMP_LHR?A?WHERE?NOT?EXISTS?(SELECT?1?FROM?DEPT_LHR?B?WHERE?B.DEPTNO=A.DEPTNO);
27-------------------------------------------------------------------------------
28|?Id??|?Operation??????????|?Name?????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|
29-------------------------------------------------------------------------------
30|???0?|?SELECT?STATEMENT???|??????????|????14?|??1400?|?????7??(15)|?00:00:01?|
31|*??1?|??HASH?JOIN?ANTI????|??????????|????14?|??1400?|?????7??(15)|?00:00:01?|
32|???2?|???TABLE?ACCESS?FULL|?EMP_LHR??|????14?|??1218?|?????3???(0)|?00:00:01?|
33|???3?|???TABLE?ACCESS?FULL|?DEPT_LHR?|?????4?|????52?|?????3???(0)|?00:00:01?|
34-------------------------------------------------------------------------------
35
36--优化器将NOT?EXISTS后的子查询做解嵌套，然后选择了哈希的反连接。这种转换属于基于代价的查询转换。下面看看NOT?IN的情况：
37SELECT?*?FROM?EMP_LHR?A?WHERE?A.DEPTNO?NOT?IN?(SELECT?B.DEPTNO?FROM?DEPT_LHR?B);
38-------------------------------------------------------------------------------
39|?Id??|?Operation??????????|?Name?????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|
40-------------------------------------------------------------------------------
41|???0?|?SELECT?STATEMENT???|??????????|????14?|??1400?|?????7??(15)|?00:00:01?|
42|*??1?|??HASH?JOIN?ANTI?NA?|??????????|????14?|??1400?|?????7??(15)|?00:00:01?|
43|???2?|???TABLE?ACCESS?FULL|?EMP_LHR??|????14?|??1218?|?????3???(0)|?00:00:01?|
44|???3?|???TABLE?ACCESS?FULL|?DEPT_LHR?|?????4?|????52?|?????3???(0)|?00:00:01?|
45-------------------------------------------------------------------------------
46

? ? ?

和NOT EXISTS类似，也选择了哈希连接，只不过是HASH JOIN ANTI NA。这里的NA，实际表示Null-Aware的意思，在11g及以后的版本中，Oracle增加了对空值敏感的反关联的支持。

（三）子查询合并（Subquery Coalesce）

 1LHR@orclasm?>?SELECT?*?FROM?EMP_LHR?A?WHERE?EXISTS?(SELECT?1?FROM?DEPT_LHR?B?WHERE?B.DEPTNO=A.DEPTNO?AND?B.DEPTNO=10)?AND?EXISTS?(SELECT?1?FROM?DEPT_LHR?B?WHERE?B.DEPTNO=A.DEPTNO?AND?B.DEPTNO=20)?;
 2
 3no?rows?selected
 4
 5
 6Execution?Plan
 7----------------------------------------------------------
 8Plan?hash?value:?3115025369
 9
10---------------------------------------------------------------------------------
11|?Id??|?Operation????????????|?Name?????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|
12---------------------------------------------------------------------------------
13|???0?|?SELECT?STATEMENT?????|??????????|?????1?|???113?|?????0???(0)|??????????|
14|*??1?|??FILTER??????????????|??????????|???????|???????|????????????|??????????|
15|*??2?|???HASH?JOIN?SEMI?????|??????????|?????1?|???113?|????10??(10)|?00:00:01?|
16|*??3?|????HASH?JOIN?SEMI????|??????????|?????1?|???100?|?????7??(15)|?00:00:01?|
17|*??4?|?????TABLE?ACCESS?FULL|?EMP_LHR??|?????1?|????87?|?????3???(0)|?00:00:01?|
18|*??5?|?????TABLE?ACCESS?FULL|?DEPT_LHR?|?????1?|????13?|?????3???(0)|?00:00:01?|
19|*??6?|????TABLE?ACCESS?FULL?|?DEPT_LHR?|?????1?|????13?|?????3???(0)|?00:00:01?|
20---------------------------------------------------------------------------------
21
22Predicate?Information?(identified?by?operation?id):
23---------------------------------------------------
24
25???1?-?filter(NULL?IS?NOT?NULL)
26???2?-?access("B"."DEPTNO"="A"."DEPTNO")
27???3?-?access("B"."DEPTNO"="A"."DEPTNO")
28???4?-?filter("A"."DEPTNO"=10?AND?"A"."DEPTNO"=20)
29???5?-?filter("B"."DEPTNO"=20?AND?"B"."DEPTNO"=10)
30???6?-?filter("B"."DEPTNO"=10?AND?"B"."DEPTNO"=20)

? ? ?

在这个查询语句中，外部查询要满足两个子查询—SUB1和SUB2，但两者条件不同，不能简单合并。因此在执行计划中，分别对两者进行了扫描（直观感觉就是对DEPT_LHR进行了两次扫描），然后再做关联查询。

 

 1LHR@orclasm?>?SELECT?*?FROM?EMP_LHR?A?WHERE?EXISTS?(SELECT?1?FROM?DEPT_LHR?B?WHERE?B.DEPTNO=A.DEPTNO?AND?B.DEPTNO=10)?AND?EXISTS?(SELECT?1?FROM?DEPT_LHR?B?WHERE?B.DEPTNO=A.DEPTNO)?;
 2
 3?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE???????????????????SAL???????COMM?????DEPTNO
 4----------?----------?---------?----------?-------------------?----------?----------?----------
 5??????7934?MILLER?????CLERK???????????7782?1982-01-23?00:00:00???????1300????????????????????10
 6??????7839?KING???????PRESIDENT????????????1981-11-17?00:00:00???????5000????????????????????10
 7??????7782?CLARK??????MANAGER?????????7839?1981-06-09?00:00:00???????2450????????????????????10
 8
 9
10Execution?Plan
11----------------------------------------------------------
12Plan?hash?value:?3403691855
13
14-------------------------------------------------------------------------------
15|?Id??|?Operation??????????|?Name?????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|
16-------------------------------------------------------------------------------
17|???0?|?SELECT?STATEMENT???|??????????|?????1?|???100?|?????7??(15)|?00:00:01?|
18|*??1?|??HASH?JOIN?SEMI????|??????????|?????1?|???100?|?????7??(15)|?00:00:01?|
19|*??2?|???TABLE?ACCESS?FULL|?EMP_LHR??|?????3?|???261?|?????3???(0)|?00:00:01?|
20|*??3?|???TABLE?ACCESS?FULL|?DEPT_LHR?|?????1?|????13?|?????3???(0)|?00:00:01?|
21-------------------------------------------------------------------------------
22
23Predicate?Information?(identified?by?operation?id):
24---------------------------------------------------
25
26???1?-?access("B"."DEPTNO"="A"."DEPTNO")
27???2?-?filter("A"."DEPTNO"=10)
28???3?-?filter("B"."DEPTNO"=10)

? ? ?

在这个查询中，外部对EMP_LHR表的查询要同时满足SUB1和SUB2两个子查询，而SUB1在语义上又是SUB2的子集，因此优化器将两个子查询进行了合并（只进行一次对DEPT_LHR表的扫描），然后与外部表EMP_LHR进行半连接。

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

? ? ?

---------------优质麦课------------

 详细内容可以添加麦老师微信或QQ私聊。

About Me：小麦苗 ? ? ?

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● QQ：646634621  QQ群：618766405

● 提供OCP、OCM和高可用部分最实用的技能培训

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

dba宝典

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。

喜欢就点击“好看”吧

本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

内容总结

以上是互联网集市为您收集整理的【DB笔试面试613】在Oracle中，和子查询相关的查询转换有哪些？全部内容，希望文章能够帮你解决【DB笔试面试613】在Oracle中，和子查询相关的查询转换有哪些？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---