【DB筆試面試613】在Oracle中，和子查詢相關的查詢轉換有哪些？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在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程式設計師面試筆試寶典》，作者：李華榮。
