查询转换——谓语前推（3）！

谓语前推用来将谓语从一个内含查询块中应用到不可合并的查询块中。目标就是允许索引的使用或者让其他对于数据集的筛选在查询中能够更早的进行。

/*+ NO_PUSH_PRED */ 禁止谓语前推。

SQL> select e1.ename, e1.sal, v.avg_sal
  2    from emp e1,
  3         (select e2.deptno, avg(e2.sal) avg_sal from emp e2 group by e2.deptno) v
  4   where e1.deptno = v.deptno
  5     and e1.sal > v.avg_sal
  6     and e1.deptno = 20;

ENAME             SAL    AVG_SAL
---------- ---------- ----------
JONES            2975       2175
SCOTT            3000       2175
FORD             3000       2175


执行计划
----------------------------------------------------------
Plan hash value: 269884559

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    39 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    39 |     7  (15)| 00:00:01 |
|   2 |   VIEW               |      |     1 |    26 |     3   (0)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     1 |     7 |     3   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| EMP  |     5 |    35 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL  | EMP  |     5 |    65 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E1"."DEPTNO"="V"."DEPTNO")
       filter("E1"."SAL">"V"."AVG_SAL")
   4 - filter("E2"."DEPTNO"=20)
   5 - filter("E1"."DEPTNO"=20)

在第4步中，deptno = 20这个谓语被推进到视图中。

SQL> select e1.ename, e1.sal, v.avg_sal
  2    from emp e1,
  3         (select e2.deptno, avg(e2.sal) avg_sal
  4            from emp e2
  5           where rownum > 0
  6           group by e2.deptno) v
  7   where e1.deptno = v.deptno
  8     and e1.sal > v.avg_sal
  9     and e1.deptno = 20;

ENAME             SAL    AVG_SAL
---------- ---------- ----------
JONES            2975       2175
SCOTT            3000       2175
FORD             3000       2175


执行计划
----------------------------------------------------------
Plan hash value: 809500627

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    39 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN             |      |     1 |    39 |     8  (25)| 00:00:01 |
|*  2 |   VIEW                 |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY       |      |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     COUNT              |      |       |       |            |          |
|*  5 |      FILTER            |      |       |       |            |          |
|   6 |       TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL    | EMP  |     5 |    65 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E1"."DEPTNO"="V"."DEPTNO")
       filter("E1"."SAL">"V"."AVG_SAL")
   2 - filter("V"."DEPTNO"=20)
   5 - filter(ROWNUM>0)
   7 - filter("E1"."DEPTNO"=20)

在这里rownum位列扮演了禁止谓语前推的角色。