【DB笔试面试615】在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')