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

小麦苗DBA宝典

发布于 2019-09-29 16:07:44

4.6K0

发布于 2019-09-29 16:07:44

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

♣

题目部分

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

♣

答案部分

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

LHR@orclasm > set serveroutput on
LHR@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');
Plan hash value: 553156288
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  3322 |   454K|  1118   (1)| 00:00:14 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | TAB$ |  3322 |   454K|   559   (1)| 00:00:07 |
|   3 |   SORT AGGREGATE    |      |     1 |     7 |            |          |
|   4 |    TABLE ACCESS FULL| IND$ |  5545 | 38815 |   559   (1)| 00:00:07 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"LHR_PS" "B"@"LHR_PS")
FULL(@"SEL$1" "A"@"SEL$1")
OUTLINE(@"LHR_PS")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"LHR_PS")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."ANALYZETIME"> (SELECT /*+ NO_PUSH_SUBQ QB_NAME
("LHR_PS") */ MAX("B"."ANALYZETIME") FROM "SYS"."IND$" "B"))

PL/SQL procedure successfully completed.

LHR@orclasm > exec sql_explain('SELECT * FROM SYS.TAB$ A WHERE A.ANALYZETIME > (SELECT MAX(B.ANALYZETIME) FROM SYS.IND$ B)','outline');
Plan hash value: 243387038
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   166 | 23240 |  1118   (1)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL  | TAB$ |   166 | 23240 |   559   (1)| 00:00:07 |
|   2 |   SORT AGGREGATE    |      |     1 |     7 |            |          |
|   3 |    TABLE ACCESS FULL| IND$ |  5545 | 38815 |   559   (1)| 00:00:07 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "B"@"SEL$2")
PUSH_SUBQ(@"SEL$2")
FULL(@"SEL$1" "A"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."ANALYZETIME"> (SELECT MAX("B"."ANALYZETIME") FROM "SYS"."IND$" "B"))

PL/SQL procedure successfully completed.

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

--1）IN和EXISTS转换为半连接（SEMI JOIN）：
CREATE TABLE EMP_LHR AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT_LHR AS SELECT * FROM SCOTT.DEPT;
SELECT * FROM EMP_LHR A WHERE EXISTS (SELECT 1 FROM DEPT_LHR B WHERE B.DEPTNO=A.DEPTNO);
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    14 |  1400 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |          |    14 |  1400 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP_LHR  |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT_LHR |     4 |    52 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

--子查询引用表DEPT，最终转换为两个表的哈希半连接。也就是说，EXISTS子句中的子查询被展开，其中的对象与主查询中的对象直接进行半关联操作。IN的情况类似，如下：
SELECT * FROM EMP_LHR A WHERE A.DEPTNO IN (SELECT B.DEPTNO FROM DEPT_LHR B);
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    14 |  1400 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |          |    14 |  1400 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP_LHR  |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT_LHR |     4 |    52 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

--2）NOT IN和NOT EXISTS转换为反连接（ANTI JOIN）：
SELECT * FROM EMP_LHR A WHERE NOT EXISTS (SELECT 1 FROM DEPT_LHR B WHERE B.DEPTNO=A.DEPTNO);
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    14 |  1400 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |          |    14 |  1400 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP_LHR  |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT_LHR |     4 |    52 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

--优化器将NOT EXISTS后的子查询做解嵌套，然后选择了哈希的反连接。这种转换属于基于代价的查询转换。下面看看NOT IN的情况：
SELECT * FROM EMP_LHR A WHERE A.DEPTNO NOT IN (SELECT B.DEPTNO FROM DEPT_LHR B);
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    14 |  1400 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |          |    14 |  1400 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP_LHR  |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT_LHR |     4 |    52 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

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

LHR@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) ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3115025369

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |   113 |     0   (0)|          |
|*  1 |  FILTER              |          |       |       |            |          |
|*  2 |   HASH JOIN SEMI     |          |     1 |   113 |    10  (10)| 00:00:01 |
|*  3 |    HASH JOIN SEMI    |          |     1 |   100 |     7  (15)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| EMP_LHR  |     1 |    87 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| DEPT_LHR |     1 |    13 |     3   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL | DEPT_LHR |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)
   2 - access("B"."DEPTNO"="A"."DEPTNO")
   3 - access("B"."DEPTNO"="A"."DEPTNO")
   4 - filter("A"."DEPTNO"=10 AND "A"."DEPTNO"=20)
   5 - filter("B"."DEPTNO"=20 AND "B"."DEPTNO"=10)
   6 - filter("B"."DEPTNO"=10 AND "B"."DEPTNO"=20)

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

LHR@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) ;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 3403691855

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   100 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |          |     1 |   100 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP_LHR  |     3 |   261 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT_LHR |     1 |    13 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("B"."DEPTNO"="A"."DEPTNO")
   2 - filter("A"."DEPTNO"=10)
   3 - filter("B"."DEPTNO"=10)

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

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

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-08-08，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle

sql

本文分享自 DB宝微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

oracle

sql

登录后参与评论

0 条评论

热度

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

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐