關於查詢轉換的一些簡單分析(三)

關於查詢轉換,已經討論了檢視合併和子查詢解巢狀，還有謂詞推進和物化檢視查詢重寫也是查詢轉換中不可或缺的部分。
--&gt謂詞推進
這個術語聽起來高大上，有點故弄玄虛的味道。其實在我們的查詢中已經潛移默化的使用到了。
比如下面的這個查詢。我們定義了一個子查詢 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來實現。

--&gt查詢重寫

對於物化檢視有幾個主要的兩點用得比較多，查詢重寫和檢視重新整理。
查詢重寫在查詢轉換中還是比較實用的。如果有海量的資料,更新頻率不高。可以根據查詢條件來定義物化檢視，開啟查詢重寫的功能。
建立一個物化檢視。
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)