--Join Predicate Pushdown
create table emp1 as select * from scott.emp

create table emp2 as select * from scott.emp

create index idx_emp1 on emp1(empno);

create index idx_emp2 on emp2(empno);

create or replace view emp_view as select
emp1.empno as empno1 from emp1
 
create or replace view emp_view_union as
select emp1.empno as empno1 from emp1 union all
select emp2.empno as empno2 from emp2

select /*+ no_merge(emp_view) */
 emp.empno
  from emp, emp_view
 where emp.empno = emp_view.empno1(+)
   and emp.ename = 'FORD'
   
   
 Plan Hash Value  : 101695337
------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |    1 |    22 |    3 | 00:00:01 |
|   1 |   NESTED LOOPS OUTER     |          |    1 |    22 |    3 | 00:00:01 |
| * 2 |    TABLE ACCESS FULL     | EMP      |    1 |    20 |    2 | 00:00:01 |
|   3 |    VIEW PUSHED PREDICATE | EMP_VIEW |    1 |     2 |    1 | 00:00:01 |
| * 4 |     INDEX RANGE SCAN     | IDX_EMP1 |    1 |    13 |    1 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("EMP"."ENAME"='FORD')
* 4 - access("EMP1"."EMPNO"="EMP"."EMPNO") --谓词推入

select /*+ no_merge(emp_view) no_push_pred(emp_view) */ --禁止谓词推入，视图合并
 emp.empno
  from emp, emp_view
 where emp.empno = emp_view.empno1(+)
   and emp.ename = 'FORD'
   
 Plan Hash Value  : 1524044994

--------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    1 |    33 |    3 | 00:00:01 |
| * 1 |   HASH JOIN OUTER    |          |    1 |    33 |    3 | 00:00:01 |
| * 2 |    TABLE ACCESS FULL | EMP      |    1 |    20 |    2 | 00:00:01 |
|   3 |    VIEW              | EMP_VIEW |   15 |   195 |    1 | 00:00:01 |
|   4 |     INDEX FULL SCAN  | IDX_EMP1 |   15 |   195 |    1 | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("EMP"."EMPNO"="EMP_VIEW"."EMPNO1"(+))
* 2 - filter("EMP"."ENAME"='FORD')

--视图中含有union all
select emp.empno
  from emp, emp_view_union
 where emp.empno = emp_view_union.empno1
   and emp.ename = 'FORD'
 Plan Hash Value  : 152695365
------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |    2 |    66 |    4 | 00:00:01 |
|   1 |   NESTED LOOPS OUTER           |                |    2 |    66 |    4 | 00:00:01 |
| * 2 |    TABLE ACCESS FULL           | EMP            |    1 |    20 |    2 | 00:00:01 |
|   3 |    VIEW /*没有进行视图合并 *|  |MP_VIEW_UNION |    1 |    13 |    2 | 00:00:01 |
|   4 |     UNION ALL PUSHED PREDICATE |  /* 谓词推入*/              |      |       |      |          |
| * 5 |      INDEX RANGE SCAN          | IDX_EMP1       |    1 |    13 |    1 | 00:00:01 |
| * 6 |      INDEX RANGE SCAN          | IDX_EMP2       |    1 |    13 |    1 | 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("EMP"."ENAME"='FORD')
* 5 - access("EMP1"."EMPNO"="EMP"."EMPNO")
* 6 - access("EMP2"."EMPNO"="EMP"."EMPNO")
Note
-----
- dynamic sampling used for this statement
--谓词推入条件;视图中含有union all，不能进行视图合并，视图基表的链接条件上已经有了索引*/

--如果禁止谓词推入，那么视图可能走出全表或者索引全扫描

select /*+ no_push_pred(emp_view_union) */
 emp.empno
  from emp, emp_view_union
 where emp.empno = emp_view_union.empno1
   and emp.ename = 'FORD'  
 Plan Hash Value  : 2461179762
--------------------------------------------------------------------------------
| Id  | Operation            | Name           | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |    2 |    66 |    4 | 00:00:01 |
| * 1 |   HASH JOIN          |                |    2 |    66 |    4 | 00:00:01 |
| * 2 |    TABLE ACCESS FULL | EMP            |    1 |    20 |    2 | 00:00:01 |
|   3 |    VIEW              | EMP_VIEW_UNION |   30 |   390 |    2 | 00:00:01 |
|   4 |     UNION-ALL        |                |      |       |      |          |
|   5 |      INDEX FULL SCAN | IDX_EMP1       |   15 |   195 |    1 | 00:00:01 |
|   6 |      INDEX FULL SCAN | IDX_EMP2       |   15 |   195 |    1 | 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("EMP"."EMPNO"="EMP_VIEW_UNION"."EMPNO1")
* 2 - filter("EMP"."ENAME"='FORD')
Note
-----
- dynamic sampling used for this statement

---CBO在做谓词推入的时候会考虑成本；如果谓词推入后成本大于之前的，那么不会进行谓词推入

select /*+ cardinality(emp 1000000) */
 emp.empno
  from emp, emp_view_union
 where emp.empno = emp_view_union.empno1
   and emp.ename = 'FORD'


 Plan Hash Value  : 1728342133
------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows    | Bytes    | Cost | Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                | 2000000 | 66000000 |   25 | 00:00:01 |
|   1 |   MERGE JOIN                   |                | 2000000 | 66000000 |   25 | 00:00:01 |
| * 2 |    TABLE ACCESS BY INDEX ROWID | EMP            | 1000000 | 20000000 |   10 | 00:00:01 |
|   3 |     INDEX FULL SCAN            | SYS_C0093796   |      15 |          |    2 | 00:00:01 |
| * 4 |    SORT JOIN                   |                |      30 |      390 |    3 | 00:00:01 |
|   5 |     VIEW                       | EMP_VIEW_UNION |      30 |      390 |    2 | 00:00:01 |
|   6 |      UNION-ALL                 |                |         |          |      |          |
|   7 |       INDEX FULL SCAN          | IDX_EMP1       |      15 |      195 |    1 | 00:00:01 |
|   8 |       INDEX FULL SCAN          | IDX_EMP2       |      15 |      195 |    1 | 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("EMP"."ENAME"='FORD')
* 4 - access("EMP"."EMPNO"="EMP_VIEW_UNION"."EMPNO1")
* 4 - filter("EMP"."EMPNO"="EMP_VIEW_UNION"."EMPNO1")

Note
-----
- dynamic sampling used for this statement

--如果此时强制谓词推入,按照oracle 成本估算大大增加
select /*+ cardinality(emp 1000000) push_pred(emp_view_union) */
 emp.empno
  from emp, emp_view_union
 where emp.empno = emp_view_union.empno1
   and emp.ename = 'FORD'
   
 Plan Hash Value  : 2223410919
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows    | Bytes    | Cost    | Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                | 1000000 | 33000000 | 2001778 | 06:40:22 |
|   1 |   NESTED LOOPS                 |                | 1000000 | 33000000 | 2001778 | 06:40:22 |
| * 2 |    TABLE ACCESS FULL           | EMP            | 1000000 | 20000000 |       2 | 00:00:01 |
|   3 |    VIEW                        | EMP_VIEW_UNION |       1 |       13 |       2 | 00:00:01 |
|   4 |     UNION ALL PUSHED PREDICATE |                |         |          |         |          |
| * 5 |      INDEX RANGE SCAN          | IDX_EMP1       |       1 |       13 |       1 | 00:00:01 |
| * 6 |      INDEX RANGE SCAN          | IDX_EMP2       |       1 |       13 |       1 | 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("EMP"."ENAME"='FORD')
* 5 - access("EMP1"."EMPNO"="EMP"."EMPNO")
* 6 - access("EMP2"."EMPNO"="EMP"."EMPNO")
Note
-----
- dynamic sampling used for this statement

---内联视图，也会进行谓词推入的
select /*+ no_merge(emp_view_inline) */
 emp.empno
  from emp, (select emp1.empno as empno1 from emp1) emp_view_inline
 where emp.empno = emp_view_inline.empno1(+)
   and emp.ename = 'FORD'
 Plan Hash Value  : 3347874242
------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |    1 |    22 |    3 | 00:00:01 |
|   1 |   NESTED LOOPS OUTER     |          |    1 |    22 |    3 | 00:00:01 |
| * 2 |    TABLE ACCESS FULL     | EMP      |    1 |    20 |    2 | 00:00:01 |
|   3 |    VIEW PUSHED PREDICATE |          |    1 |     2 |    1 | 00:00:01 |
| * 4 |     INDEX RANGE SCAN     | IDX_EMP1 |    1 |    13 |    1 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("EMP"."ENAME"='FORD')
* 4 - access("EMP1"."EMPNO"="EMP"."EMPNO")
Note
-----
- dynamic sampling used for this statement
/*   能否做谓词推入与目标视图是否能做视图合并，是否是内联视图没有关系
但是与目标视图的类型，与外部查询之间的链接类型以及链接方法有关系 仅包含以下几种
视图定义中含有union all，union， distinct，group by，和外部查询之间是外连接，
反连接，半连接*/

--以下就无法做谓词推入,即使加入HINT

select /*+ no_merge(emp_view) push_pred(emp_view) */
 emp.empno
  from emp, emp_view
 where emp.empno = emp_view.empno1
   and emp.ename = 'FORD'