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

DB宝 2019-08-10

596

♣题目部分在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---------------------------------------------------
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-------------------------------------------------------------------------------------------------
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程序员面试笔试宝典》，作者：李华荣。