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

• 2019 年 10 月 10 日
• 筆記

题目部分

在Oracle中，和谓词相关的查询转换有哪些？

♣

答案部分

（一）过滤谓词推入

LHR@orclasm > SELECT * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA';    --------------------------------------------------------------------------  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  --------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |      |     3 |   114 |     3   (0)| 00:00:01 |  |*  1 |  TABLE ACCESS FULL| EMP  |     3 |   114 |     3   (0)| 00:00:01 |  --------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------       1 - filter("JOB"='DBA' AND "EMPNO"<>7369)    LHR@orclasm > SELECT /*+NO_MERGE(WV)*/ * FROM (SELECT * FROM VW_SVM_LHR V) WV WHERE WV.JOB='DBA';    no rows selected      Execution Plan  ----------------------------------------------------------  Plan hash value: 2734967094    ---------------------------------------------------------------------------  | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------  |   0 | SELECT STATEMENT   |      |     3 |   261 |     3   (0)| 00:00:01 |  |   1 |  VIEW              |      |     3 |   261 |     3   (0)| 00:00:01 |  |*  2 |   TABLE ACCESS FULL| EMP  |     3 |   114 |     3   (0)| 00:00:01 |  ---------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - filter("JOB"='DBA' AND "EMPNO"<>7369)  

（二）连接谓词推入

LHR@orclasm > SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';  -------------------------------------------------------------------------------------------------  | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT             |                  |     1 |    51 |     2   (0)| 00:00:01 |  |   1 |  NESTED LOOPS OUTER          |                  |     1 |    51 |     2   (0)| 00:00:01 |  |   2 |   TABLE ACCESS BY INDEX ROWID| EMP              |     1 |    38 |     2   (0)| 00:00:01 |  |*  3 |    INDEX SKIP SCAN           | IDX_FULL_EMP_LHR |     1 |       |     1   (0)| 00:00:01 |  |   4 |   VIEW PUSHED PREDICATE      | VW_JPPD_LHR      |     1 |    13 |     0   (0)| 00:00:01 |  |*  5 |    INDEX UNIQUE SCAN         | PK_EMP           |     1 |     4 |     0   (0)| 00:00:01 |  -------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       3 - access("T"."ENAME"='DBA')         filter("T"."ENAME"='DBA')     5 - access("T"."EMPNO"="T"."EMPNO")    LHR@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';    -------------------------------------------------------------------------------------------------  | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT             |                  |     1 |    51 |     4  (25)| 00:00:01 |  |*  1 |  HASH JOIN OUTER             |                  |     1 |    51 |     4  (25)| 00:00:01 |  |   2 |   TABLE ACCESS BY INDEX ROWID| EMP              |     1 |    38 |     2   (0)| 00:00:01 |  |*  3 |    INDEX SKIP SCAN           | IDX_FULL_EMP_LHR |     1 |       |     1   (0)| 00:00:01 |  |   4 |   VIEW                       | VW_JPPD_LHR      |    14 |   182 |     1   (0)| 00:00:01 |  |   5 |    INDEX FULL SCAN           | IDX_FULL_EMP_LHR |    14 |    56 |     1   (0)| 00:00:01 |  -------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - access("T"."EMPNO"="V"."EMPNO"(+))     3 - access("T"."ENAME"='DBA')         filter("T"."ENAME"='DBA')    LHR@orclasm > ALTER SESSION SET "_PUSH_JOIN_PREDICATE"=FALSE;    Session altered.    LHR@orclasm > SELECT /*+NO_MERGE(V)*/ * FROM SCOTT.EMP T,VW_JPPD_LHR V WHERE T.EMPNO=V.EMPNO(+) AND T.ENAME='DBA';  -------------------------------------------------------------------------------------------------  | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT             |                  |     1 |    51 |     4  (25)| 00:00:01 |  |*  1 |  HASH JOIN OUTER             |                  |     1 |    51 |     4  (25)| 00:00:01 |  |   2 |   TABLE ACCESS BY INDEX ROWID| EMP              |     1 |    38 |     2   (0)| 00:00:01 |  |*  3 |    INDEX SKIP SCAN           | IDX_FULL_EMP_LHR |     1 |       |     1   (0)| 00:00:01 |  |   4 |   VIEW                       | VW_JPPD_LHR      |    14 |   182 |     1   (0)| 00:00:01 |  |   5 |    INDEX FULL SCAN           | IDX_FULL_EMP_LHR |    14 |    56 |     1   (0)| 00:00:01 |  -------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - access("T"."EMPNO"="V"."EMPNO"(+))     3 - access("T"."ENAME"='DBA')         filter("T"."ENAME"='DBA')  

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。