且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

关于查询转换的一些简单分析(三)

更新时间:2022-01-08 03:57:45

关于查询转换,已经讨论了视图合并和子查询解嵌套,还有谓词推进和物化视图查询重写也是查询转换中不可或缺的部分。
-->谓词推进
这个术语听起来高大上,有点故弄玄虚的味道。其实在我们的查询中已经潜移默化的使用到了。
比如下面的这个查询。我们定义了一个子查询 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
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

 这个执行计划中,因为使用了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)