在Oracle中，和谓词相关的查询转换有哪些？
♣答案部分
（一）过滤谓词推入
1LHR@orclasm > SELECT * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA'; 2 3-------------------------------------------------------------------------- 4| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 5-------------------------------------------------------------------------- 6| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 | 7|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 | 8-------------------------------------------------------------------------- 9Predicate Information (identified by operation id):10---------------------------------------------------1112 1 - filter("JOB"='DBA' AND "EMPNO"<>7369)1314LHR@orclasm > SELECT /*+NO_MERGE(WV)*/ * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA';1516no rows selected171819Execution Plan20----------------------------------------------------------21Plan hash value: 27349670942223---------------------------------------------------------------------------24| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |25---------------------------------------------------------------------------26| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 |27| 1 | VIEW | | 3 | 261 | 3 (0)| 00:00:01 |28|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |29---------------------------------------------------------------------------3031Predicate Information (identified by operation id):32---------------------------------------------------3334 2 - filter("JOB"='DBA' AND "EMPNO"<>7369)SELECT * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA'; 2 3-------------------------------------------------------------------------- 4| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 5-------------------------------------------------------------------------- 6| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 | 7|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 | 8-------------------------------------------------------------------------- 9Predicate Information (identified by operation id): 10--------------------------------------------------- 11 12 1 - filter("JOB"='DBA' AND "EMPNO"<>7369) 13 14LHR@orclasm > SELECT /*+NO_MERGE(WV)*/ * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA'; 15 16no rows selected 17 18 19Execution Plan 20---------------------------------------------------------- 21Plan hash value: 2734967094 22 23--------------------------------------------------------------------------- 24| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 25--------------------------------------------------------------------------- 26| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 | 27| 1 | VIEW | | 3 | 261 | 3 (0)| 00:00:01 | 28|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 | 29--------------------------------------------------------------------------- 30 31Predicate Information (identified by operation id): 32--------------------------------------------------- 33 34 2 - filter("JOB"='DBA' AND "EMPNO"<>7369)
（二）连接谓词推入
1LHR@orclasm > SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA'; 2------------------------------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4------------------------------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 1 | 51 | 2 (0)| 00:00:01 | 6| 1 | NESTED LOOPS OUTER | | 1 | 51 | 2 (0)| 00:00:01 | 7| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | 8|* 3 | INDEX SKIP SCAN | IDX_FULL_EMP_LHR | 1 | | 1 (0)| 00:00:01 | 9| 4 | VIEW PUSHED PREDICATE | VW_JPPD_LHR | 1 | 13 | 0 (0)| 00:00:01 |10|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |11-------------------------------------------------------------------------------------------------1213Predicate Information (identified by operation id):14---------------------------------------------------1516 3 - access("T"."ENAME"='DBA')17 filter("T"."ENAME"='DBA')18 5 - access("T"."EMPNO"="T"."EMPNO")1920LHR@orclasm > SELECT /*+NO_MERGE(V) NO_PUSH_PRED(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';2122-------------------------------------------------------------------------------------------------23| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |24-------------------------------------------------------------------------------------------------25| 0 | SELECT STATEMENT | | 1 | 51 | 4 (25)| 00:00:01 |26|* 1 | HASH JOIN OUTER | | 1 | 51 | 4 (25)| 00:00:01 |27| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |28|* 3 | INDEX SKIP SCAN | IDX_FULL_EMP_LHR | 1 | | 1 (0)| 00:00:01 |29| 4 | VIEW | VW_JPPD_LHR | 14 | 182 | 1 (0)| 00:00:01 |30| 5 | INDEX FULL SCAN | IDX_FULL_EMP_LHR | 14 | 56 | 1 (0)| 00:00:01 |31-------------------------------------------------------------------------------------------------3233Predicate Information (identified by operation id):34---------------------------------------------------3536 1 - access("T"."EMPNO"="V"."EMPNO"(+))37 3 - access("T"."ENAME"='DBA')38 filter("T"."ENAME"='DBA')3940LHR@orclasm > ALTER SESSION SET "_PUSH_JOIN_PREDICATE"=FALSE; 4142Session altered.4344LHR@orclasm > SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';45-------------------------------------------------------------------------------------------------46| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |47-------------------------------------------------------------------------------------------------48| 0 | SELECT STATEMENT | | 1 | 51 | 4 (25)| 00:00:01 |49|* 1 | HASH JOIN OUTER | | 1 | 51 | 4 (25)| 00:00:01 |50| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |51|* 3 | INDEX SKIP SCAN | IDX_FULL_EMP_LHR | 1 | | 1 (0)| 00:00:01 |52| 4 | VIEW | VW_JPPD_LHR | 14 | 182 | 1 (0)| 00:00:01 |53| 5 | INDEX FULL SCAN | IDX_FULL_EMP_LHR | 14 | 56 | 1 (0)| 00:00:01 |54-------------------------------------------------------------------------------------------------5556Predicate Information (identified by operation id):57---------------------------------------------------5859 1 - access("T"."EMPNO"="V"."EMPNO"(+))60 3 - access("T"."ENAME"='DBA')61 filter("T"."ENAME"='DBA')SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA'; 2------------------------------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4------------------------------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 1 | 51 | 2 (0)| 00:00:01 | 6| 1 | NESTED LOOPS OUTER | | 1 | 51 | 2 (0)| 00:00:01 | 7| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | 8|* 3 | INDEX SKIP SCAN | IDX_FULL_EMP_LHR | 1 | | 1 (0)| 00:00:01 | 9| 4 | VIEW PUSHED PREDICATE | VW_JPPD_LHR | 1 | 13 | 0 (0)| 00:00:01 | 10|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | 4 | 0 (0)| 00:00:01 | 11------------------------------------------------------------------------------------------------- 12 13Predicate Information (identified by operation id): 14--------------------------------------------------- 15 16 3 - access("T"."ENAME"='DBA') 17 filter("T"."ENAME"='DBA') 18 5 - access("T"."EMPNO"="T"."EMPNO") 19 20LHR@orclasm > SELECT /*+NO_MERGE(V) NO_PUSH_PRED(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA'; 21 22------------------------------------------------------------------------------------------------- 23| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 24------------------------------------------------------------------------------------------------- 25| 0 | SELECT STATEMENT | | 1 | 51 | 4 (25)| 00:00:01 | 26|* 1 | HASH JOIN OUTER | | 1 | 51 | 4 (25)| 00:00:01 | 27| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | 28|* 3 | INDEX SKIP SCAN | IDX_FULL_EMP_LHR | 1 | | 1 (0)| 00:00:01 | 29| 4 | VIEW | VW_JPPD_LHR | 14 | 182 | 1 (0)| 00:00:01 | 30| 5 | INDEX FULL SCAN | IDX_FULL_EMP_LHR | 14 | 56 | 1 (0)| 00:00:01 | 31------------------------------------------------------------------------------------------------- 32 33Predicate Information (identified by operation id): 34--------------------------------------------------- 35 36 1 - access("T"."EMPNO"="V"."EMPNO"(+)) 37 3 - access("T"."ENAME"='DBA') 38 filter("T"."ENAME"='DBA') 39 40LHR@orclasm > ALTER SESSION SET "_PUSH_JOIN_PREDICATE"=FALSE; 41 42Session altered. 43 44LHR@orclasm > SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA'; 45------------------------------------------------------------------------------------------------- 46| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 47------------------------------------------------------------------------------------------------- 48| 0 | SELECT STATEMENT | | 1 | 51 | 4 (25)| 00:00:01 | 49|* 1 | HASH JOIN OUTER | | 1 | 51 | 4 (25)| 00:00:01 | 50| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | 51|* 3 | INDEX SKIP SCAN | IDX_FULL_EMP_LHR | 1 | | 1 (0)| 00:00:01 | 52| 4 | VIEW | VW_JPPD_LHR | 14 | 182 | 1 (0)| 00:00:01 | 53| 5 | INDEX FULL SCAN | IDX_FULL_EMP_LHR | 14 | 56 | 1 (0)| 00:00:01 | 54------------------------------------------------------------------------------------------------- 55 56Predicate Information (identified by operation id): 57--------------------------------------------------- 58 59 1 - access("T"."EMPNO"="V"."EMPNO"(+)) 60 3 - access("T"."ENAME"='DBA') 61 filter("T"."ENAME"='DBA')
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧
