公众号：杨建荣的学习笔记 · 作者：r3笔记第69天 · 发布：2014-11-29 20:32:56 · 原文链接

关于查询转换,已经讨论了视图合并和子查询解嵌套，还有谓词推进和物化视图查询重写也是查询转换中不可或缺的部分。
–>谓词推进
这个术语听起来高大上，有点故弄玄虚的味道。其实在我们的查询中已经潜移默化的使用到了。
比如下面的这个查询。我们定义了一个子查询 v，然后在外部的过滤条件中加入了过滤条件e.deptno=10
因为e.deptno=v.deptno 所以过滤条件也可以理解为v.depetno=10

select e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal) avg_salary
from emp oe
group by deptno)v
where e.deptno=v.deptno and e.sal>1000
and e.deptno=10

Execution Plan
———————————————————-
Plan hash value: 269884559

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

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

1 – access(“E”.”DEPTNO”=”V”.”DEPTNO”)
4 – filter(“DEPTNO”=10)
5 – filter(“E”.”DEPTNO”=10 AND “E”.”SAL”>1000)

从上面的执行计划可以看到执行计划第2,3,4行是子查询v 相关的，第4行中的过滤条件，直接在子查询中嵌入了过滤条件depetno=10。 这种情况就是使用了谓词推进。
谓词推进在查询中都是自动调用的。但是如果碰到了rownum,算是它的死穴。我们来看看使用了rownum之后的执行计划。

select e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal) avg_salary
from emp oe where rownum<100
group by deptno )v
where e.deptno=v.deptno and e.sal>1000
and e.deptno=10

Execution Plan
———————————————————-
Plan hash value: 1823415238

——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 9 | 351 | 6 (34)| 00:00:01 |
|* 1 | HASH JOIN | | 9 | 351 | 6 (34)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 3 | 39 | 2 (0)| 00:00:01 |
|* 3 | VIEW | | 3 | 78 | 3 (34)| 00:00:01 |
| 4 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
——————————————————————————

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

1 – access(“E”.”DEPTNO”=”V”.”DEPTNO”)
2 – filter(“E”.”DEPTNO”=10 AND “E”.”SAL”>1000)
3 – filter(“V”.”DEPTNO”=10)
5 – filter(ROWNUM<100)

这个执行计划中，因为使用了rownum，所以就没有使用到谓词推进。
对于谓词推进也可以考虑使用hint push_pred来实现。

–>查询重写

对于物化视图有几个主要的两点用得比较多，查询重写和视图刷新。
查询重写在查询转换中还是比较实用的。如果有海量的数据,更新频率不高。可以根据查询条件来定义物化视图，开启查询重写的功能。
创建一个物化视图。
create materialized view mv_emp enable query rewrite
as
select e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal) avg_salary
from emp oe
group by deptno )v
where e.deptno=v.deptno and e.sal>1000
and e.deptno=10

然后使用查询,执行计划如下：
Execution Plan
———————————————————-
Plan hash value: 2244303076

—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 3 | 99 | 2 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_EMP | 3 | 99 | 2 (0)| 00:00:01 |
—————————————————————————————

Note
—–
– dynamic sampling used for this statement (level=2)