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

2022-10-19 22:28:19

♣题目部分

在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 Data14-------------15/*+16BEGIN_OUTLINE_DATA17FULL(@"LHR_PS" "B"@"LHR_PS")18FULL(@"SEL$1" "A"@"SEL$1")19OUTLINE(@"LHR_PS")20OUTLINE_LEAF(@"SEL$1")21OUTLINE_LEAF(@"LHR_PS")22ALL_ROWS23DB_VERSION('11.2.0.3')24OPTIMIZER_FEATURES_ENABLE('11.2.0.3')25IGNORE_OPTIM_EMBEDDED_HINTS26END_OUTLINE_DATA27*/28Predicate Information (identified by operation id):29---------------------------------------------------301 - filter("A"."ANALYZETIME"> (SELECT /*+ NO_PUSH_SUBQ QB_NAME31("LHR_PS") */ MAX("B"."ANALYZETIME") FROM "SYS"."IND$" "B"))3233PL/SQL procedure successfully completed.3435LHR@orclasm > exec sql_explain('SELECT * FROM SYS.TAB$ A WHERE A.ANALYZETIME > (SELECT MAX(B.ANALYZETIME) FROM SYS.IND$ B)','outline');36Plan hash value: 24338703837----------------------------------------------------------------------------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 Data46-------------47/*+48BEGIN_OUTLINE_DATA49FULL(@"SEL$2" "B"@"SEL$2")50PUSH_SUBQ(@"SEL$2")51FULL(@"SEL$1" "A"@"SEL$1")52OUTLINE_LEAF(@"SEL$1")53OUTLINE_LEAF(@"SEL$2")54ALL_ROWS55DB_VERSION('11.2.0.3')56OPTIMIZER_FEATURES_ENABLE('11.2.0.3')57IGNORE_OPTIM_EMBEDDED_HINTS58END_OUTLINE_DATA59*/60Predicate Information (identified by operation id):61---------------------------------------------------621 - filter("A"."ANALYZETIME"> (SELECT MAX("B"."ANALYZETIME") FROM "SYS"."IND$" "B"))6364PL/SQL procedure successfully completed.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-------------------------------------------------------------------------------1314--子查询引用表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-------------------------------------------------------------------------------2425--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-------------------------------------------------------------------------------3536--优化器将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--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 910---------------------------------------------------------------------------------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---------------------------------------------------------------------------------2122Predicate Information (identified by operation id):23---------------------------------------------------2425   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)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 910Execution Plan11----------------------------------------------------------12Plan hash value: 34036918551314-------------------------------------------------------------------------------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-------------------------------------------------------------------------------2223Predicate Information (identified by operation id):24---------------------------------------------------2526   1 - access("B"."DEPTNO"="A"."DEPTNO")27   2 - filter("A"."DEPTNO"=10)28   3 - filter("B"."DEPTNO"=10)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进行半连接。

码农公寓

相关文章