【DB笔试面试616】在Oracle中，和“消除”相关的查询转换有哪些？
- 2019 年 10 月 10 日
- 筆記
题目部分
在Oracle中，和“消除”相关的查询转换有哪些？
♣
答案部分
（一）排序消除
LHR@orclasm > SELECT COUNT(1) FROM ( SELECT T.EMPNO FROM SCOTT.EMP T ORDER BY T.EMPNO); COUNT(1) ---------- 14 Execution Plan ---------------------------------------------------------- Plan hash value: 96606410 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| IDX_FULL_EMP_LHR | 14 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 47 recursive calls 5 db block gets 1 consistent gets 0 physical reads 2616 redo size 526 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
（二）去重消除
CREATE TABLE T_QC_20170613_LHR AS SELECT * FROM DBA_USERS; LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T; Execution Plan ---------------------------------------------------------- Plan hash value: 1708573004 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 58 | 986 | 4 (25)| 00:00:01 | | 1 | HASH UNIQUE | | 58 | 986 | 4 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL| T_QC_20170613_LHR | 58 | 986 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 6 recursive calls 0 db block gets 9 consistent gets 1 physical reads 0 redo size 1710 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 58 rows processed ALTER TABLE T_QC_20170613_LHR ADD PRIMARY KEY (USERNAME); LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T; Execution Plan ---------------------------------------------------------- Plan hash value: 884813832 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 58 | 986 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | SYS_C0089569 | 58 | 986 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 79 recursive calls 28 db block gets 83 consistent gets 0 physical reads 0 redo size 1710 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 58 rows processed
（三）表消除
SELECT A.* FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO; -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"."DEPTNO" IS NOT NULL)
（四）公共子表达式消除（Common Sub-expression Elimination，CSE）
LHR@orclasm > SELECT * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE ( A.DEPTNO=B.DEPTNO AND A.EMPNO<=7521) OR ( A.DEPTNO=B.DEPTNO AND A.EMPNO>=7782 ); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 1287 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 11 | 1287 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 11 | 957 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."DEPTNO"="B"."DEPTNO") 3 - filter("A"."EMPNO"<=7521 OR "A"."EMPNO">=7782) LHR@orclasm > ALTER SESSION SET "_ELIMINATE_COMMON_SUBEXPR"=FALSE; Session altered. LHR@orclasm > SELECT * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE ( A.DEPTNO=B.DEPTNO AND A.EMPNO<=7521) OR ( A.DEPTNO=B.DEPTNO AND A.EMPNO>=7782 ); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 117 | 10 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 117 | 10 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO"<=7521 OR "A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO">=7782)
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
