在Oracle中，和“消除”相关的查询转换有哪些？
♣答案部分
（一）排序消除
1LHR@orclasm > SELECT COUNT(1) FROM ( SELECT T.EMPNO FROM SCOTT.EMP T ORDER BY T.EMPNO); 2 3 COUNT(1) 4---------- 5 14 6 7 8Execution Plan 9----------------------------------------------------------10Plan hash value: 966064101112-----------------------------------------------------------------------------13| Id | Operation | Name | Rows | Cost (%CPU)| Time |14-----------------------------------------------------------------------------15| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |16| 1 | SORT AGGREGATE | | 1 | | |17| 2 | INDEX FULL SCAN| IDX_FULL_EMP_LHR | 14 | 1 (0)| 00:00:01 |18-----------------------------------------------------------------------------192021Statistics22----------------------------------------------------------23 47 recursive calls24 5 db block gets25 1 consistent gets26 0 physical reads27 2616 redo size28 526 bytes sent via SQL*Net to client29 519 bytes received via SQL*Net from client30 2 SQL*Net roundtrips to/from client31 0 sorts (memory)32 0 sorts (disk)33 1 rows processedSELECT COUNT(1) FROM ( SELECT T.EMPNO FROM SCOTT.EMP T ORDER BY T.EMPNO); 2 3 COUNT(1) 4---------- 5 14 6 7 8Execution Plan 9---------------------------------------------------------- 10Plan hash value: 96606410 11 12----------------------------------------------------------------------------- 13| Id | Operation | Name | Rows | Cost (%CPU)| Time | 14----------------------------------------------------------------------------- 15| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | 16| 1 | SORT AGGREGATE | | 1 | | | 17| 2 | INDEX FULL SCAN| IDX_FULL_EMP_LHR | 14 | 1 (0)| 00:00:01 | 18----------------------------------------------------------------------------- 19 20 21Statistics 22---------------------------------------------------------- 23 47 recursive calls 24 5 db block gets 25 1 consistent gets 26 0 physical reads 27 2616 redo size 28 526 bytes sent via SQL*Net to client 29 519 bytes received via SQL*Net from client 30 2 SQL*Net roundtrips to/from client 31 0 sorts (memory) 32 0 sorts (disk) 33 1 rows processed
（二）去重消除
1CREATE TABLE T_QC_20170613_LHR AS SELECT * FROM DBA_USERS; 2 3LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T; 4 5Execution Plan 6---------------------------------------------------------- 7Plan hash value: 1708573004 8 9----------------------------------------------------------------------------------------10| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |11----------------------------------------------------------------------------------------12| 0 | SELECT STATEMENT | | 58 | 986 | 4 (25)| 00:00:01 |13| 1 | HASH UNIQUE | | 58 | 986 | 4 (25)| 00:00:01 |14| 2 | TABLE ACCESS FULL| T_QC_20170613_LHR | 58 | 986 | 3 (0)| 00:00:01 |15----------------------------------------------------------------------------------------1617Note18-----19 - dynamic sampling used for this statement (level=2)202122Statistics23----------------------------------------------------------24 6 recursive calls25 0 db block gets26 9 consistent gets27 1 physical reads28 0 redo size29 1710 bytes sent via SQL*Net to client30 552 bytes received via SQL*Net from client31 5 SQL*Net roundtrips to/from client32 0 sorts (memory)33 0 sorts (disk)34 58 rows processed3536ALTER TABLE T_QC_20170613_LHR ADD PRIMARY KEY (USERNAME);3738LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T; 3940Execution Plan41----------------------------------------------------------42Plan hash value: 8848138324344---------------------------------------------------------------------------------45| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |46---------------------------------------------------------------------------------47| 0 | SELECT STATEMENT | | 58 | 986 | 1 (0)| 00:00:01 |48| 1 | INDEX FULL SCAN | SYS_C0089569 | 58 | 986 | 1 (0)| 00:00:01 |49---------------------------------------------------------------------------------5051Note52-----53 - dynamic sampling used for this statement (level=2)545556Statistics57----------------------------------------------------------58 79 recursive calls59 28 db block gets60 83 consistent gets61 0 physical reads62 0 redo size63 1710 bytes sent via SQL*Net to client64 552 bytes received via SQL*Net from client65 5 SQL*Net roundtrips to/from client66 6 sorts (memory)67 0 sorts (disk)68 58 rows processedCREATE TABLE T_QC_20170613_LHR AS SELECT * FROM DBA_USERS; 2 3LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T; 4 5Execution Plan 6---------------------------------------------------------- 7Plan hash value: 1708573004 8 9---------------------------------------------------------------------------------------- 10| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 11---------------------------------------------------------------------------------------- 12| 0 | SELECT STATEMENT | | 58 | 986 | 4 (25)| 00:00:01 | 13| 1 | HASH UNIQUE | | 58 | 986 | 4 (25)| 00:00:01 | 14| 2 | TABLE ACCESS FULL| T_QC_20170613_LHR | 58 | 986 | 3 (0)| 00:00:01 | 15---------------------------------------------------------------------------------------- 16 17Note 18----- 19 - dynamic sampling used for this statement (level=2) 20 21 22Statistics 23---------------------------------------------------------- 24 6 recursive calls 25 0 db block gets 26 9 consistent gets 27 1 physical reads 28 0 redo size 29 1710 bytes sent via SQL*Net to client 30 552 bytes received via SQL*Net from client 31 5 SQL*Net roundtrips to/from client 32 0 sorts (memory) 33 0 sorts (disk) 34 58 rows processed 35 36ALTER TABLE T_QC_20170613_LHR ADD PRIMARY KEY (USERNAME); 37 38LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T; 39 40Execution Plan 41---------------------------------------------------------- 42Plan hash value: 884813832 43 44--------------------------------------------------------------------------------- 45| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 46--------------------------------------------------------------------------------- 47| 0 | SELECT STATEMENT | | 58 | 986 | 1 (0)| 00:00:01 | 48| 1 | INDEX FULL SCAN | SYS_C0089569 | 58 | 986 | 1 (0)| 00:00:01 | 49--------------------------------------------------------------------------------- 50 51Note 52----- 53 - dynamic sampling used for this statement (level=2) 54 55 56Statistics 57---------------------------------------------------------- 58 79 recursive calls 59 28 db block gets 60 83 consistent gets 61 0 physical reads 62 0 redo size 63 1710 bytes sent via SQL*Net to client 64 552 bytes received via SQL*Net from client 65 5 SQL*Net roundtrips to/from client 66 6 sorts (memory) 67 0 sorts (disk) 68 58 rows processed
（三）表消除
1SELECT A.* FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO; 2-------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4-------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 | 6|* 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 | 7-------------------------------------------------------------------------- 8 9Predicate Information (identified by operation id):10---------------------------------------------------1112 1 - filter("A"."DEPTNO" IS NOT NULL)SELECT A.* FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO; 2-------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4-------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 | 6|* 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 | 7-------------------------------------------------------------------------- 8 9Predicate Information (identified by operation id): 10--------------------------------------------------- 11 12 1 - filter("A"."DEPTNO" IS NOT NULL)
（四）公共子表达式消除（Common Sub-expression Elimination，CSE）
1LHR@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 ); 2 3--------------------------------------------------------------------------- 4| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 5--------------------------------------------------------------------------- 6| 0 | SELECT STATEMENT | | 11 | 1287 | 7 (15)| 00:00:01 | 7|* 1 | HASH JOIN | | 11 | 1287 | 7 (15)| 00:00:01 | 8| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | 9|* 3 | TABLE ACCESS FULL| EMP | 11 | 957 | 3 (0)| 00:00:01 |10---------------------------------------------------------------------------1112Predicate Information (identified by operation id):13---------------------------------------------------1415 1 - access("A"."DEPTNO"="B"."DEPTNO")16 3 - filter("A"."EMPNO"<=7521 OR "A"."EMPNO">=7782)1718LHR@orclasm > ALTER SESSION SET "_ELIMINATE_COMMON_SUBEXPR"=FALSE; 1920Session altered.2122LHR@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 );2324---------------------------------------------------------------------------25| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |26---------------------------------------------------------------------------27| 0 | SELECT STATEMENT | | 1 | 117 | 10 (0)| 00:00:01 |28| 1 | NESTED LOOPS | | 1 | 117 | 10 (0)| 00:00:01 |29| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |30|* 3 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |31---------------------------------------------------------------------------3233Predicate Information (identified by operation id):34---------------------------------------------------3536 3 - filter("A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO"<=7521 OR37 "A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO">=7782)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 ); 2 3--------------------------------------------------------------------------- 4| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 5--------------------------------------------------------------------------- 6| 0 | SELECT STATEMENT | | 11 | 1287 | 7 (15)| 00:00:01 | 7|* 1 | HASH JOIN | | 11 | 1287 | 7 (15)| 00:00:01 | 8| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | 9|* 3 | TABLE ACCESS FULL| EMP | 11 | 957 | 3 (0)| 00:00:01 | 10--------------------------------------------------------------------------- 11 12Predicate Information (identified by operation id): 13--------------------------------------------------- 14 15 1 - access("A"."DEPTNO"="B"."DEPTNO") 16 3 - filter("A"."EMPNO"<=7521 OR "A"."EMPNO">=7782) 17 18LHR@orclasm > ALTER SESSION SET "_ELIMINATE_COMMON_SUBEXPR"=FALSE; 19 20Session altered. 21 22LHR@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 ); 23 24--------------------------------------------------------------------------- 25| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 26--------------------------------------------------------------------------- 27| 0 | SELECT STATEMENT | | 1 | 117 | 10 (0)| 00:00:01 | 28| 1 | NESTED LOOPS | | 1 | 117 | 10 (0)| 00:00:01 | 29| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | 30|* 3 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 | 31--------------------------------------------------------------------------- 32 33Predicate Information (identified by operation id): 34--------------------------------------------------- 35 36 3 - filter("A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO"<=7521 OR 37 "A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO">=7782)
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧
