在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')