隱式轉(zhuǎn)換影響物化視圖查詢重寫

有人提出過關于一個物化視圖無法查詢重寫的問題。不過通過診斷最終發(fā)現(xiàn)，問題其實并不是物化視圖的功能所導致的，真正的原因是由于引入了隱式轉(zhuǎn)換。

關于物化視圖的PCT快速刷新能力，以及PCT查詢重寫功能可以參考這篇文章：http://yangtingkun.itpub.net/post/468/21406。

測試案例

原始的案例比較復雜，還是通過一個簡化后的例子來說明這個問題：

    SQL＞ create table t (
      2  id number,
      3  time date,
      4  other varchar2(4000))
      5  partition by range (time)
      6  (partition p1 values less than (to_date('2008-1-1', 'yyyy-mm-dd')),
      7  partition p2 values less than (to_date('2009-1-1', 'yyyy-mm-dd')),
      8  partition p3 values less than (to_date('2010-1-1', 'yyyy-mm-dd')),
      9  partition p4 values less than (to_date('2011-1-1', 'yyyy-mm-dd')));
    Table created.
    SQL＞ insert into t
      2  select rownum, sysdate - rownum, lpad('a', 4000, 'a')
      3  from dba_objects;
    76162 rows created.
    SQL＞ create materialized view log on t
      2  with rowid, sequence
      3  (id, time)
      4  including new values;
    Materialized view log created.
    SQL＞ create materialized view mv_t
      2  refresh fast
      3  enable query rewrite as
      4  select time, count(＊)
      5  from t
      6  group by time;
    Materialized view created.

首先建立物化視圖的基表，插入一些測試數(shù)據(jù)，之后建立物化視圖日志和快速刷新且支持查詢重寫的物化視圖，然后檢查這個物化視圖當前是否支持查詢重寫：

    SQL＞ set autot on exp
    SQL＞ select time, count(＊)
      2  from t
      3  where time ＞ to_date('2009-1-1', 'yyyy-mm-dd')
      4  and time ＜ to_date('2009-1-10', 'yyyy-mm-dd')
      5  group by time;
    TIME                COUNT(＊)
    -------------- ----------
    04-1月 -09              1
    09-1月 -09              1
    01-1月 -09              1
    05-1月 -09              1
    03-1月 -09              1
    02-1月 -09              1
    08-1月 -09              1
    07-1月 -09              1
    06-1月 -09              1
    9 rows selected.
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1712400360
    -------------------------------------------------------------------------------------
    | Id  | Operation                     | Name| Rows  | Bytes  | Cost (%CPU) | Time     |
    -------------------------------------------------  ------------------------------------
    |   0 | SELECT STATEMENT              |     |    9  |   198  |    33   (4)  | 00:00:01 |
    |＊  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T|    9  |   198  |    33   (4)  | 00:00:01 |
    -------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1- filter("MV_T"."TIME"＞TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd
                hh24:mi:ss') AND "MV_T"."TIME"＜TO_DATE('2009-01-10 00:00:00', 'yyyy-mm-dd
                hh24:mi:ss'))
    Note
    -----
       - dynamic sampling used for this statement

由于物化視圖支持查詢重寫，而且物化視圖的數(shù)據(jù)是最新的，Oracle的CBO在分析后認為查詢物化視圖的代價要比直接查詢基表的代價低，因此選擇了查詢物化視圖。

對基表進行DML操作，DML語句并不影響當前查詢的分區(qū)：

    SQL＞ set autot off
    SQL＞ delete t where time ＜ to_date('2008-1-1', 'yyyy-mm-dd');
    75278 rows deleted.
    SQL＞ commit;
    Commit complete.
    SQL＞ set autot on exp
    SQL＞ select time, count(＊)
      2  from t
      3  where time ＞ to_date('2009-1-1', 'yyyy-mm-dd')
      4  and time ＜ to_date('2009-1-10', 'yyyy-mm-dd')
      5  group by time;
    TIME              COUNT(＊)
    -------------- ----------
    04-1月 -09              1
    09-1月 -09              1
    01-1月 -09              1
    05-1月 -09              1
    03-1月 -09              1
    02-1月 -09              1
    08-1月 -09              1
    07-1月 -09              1
    06-1月 -09              1
    9 rows selected.
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1712400360
    -------------------------------------------------------------------------------------
    | Id  | Operation                     | Name | Rows | Bytes | Cost (%CPU)| Time      |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |      |    9 |   198 |    33   (4)  | 00:00:01  |
    |＊  1 |  MAT_VIEW REWRITE ACCESS FULL | MV_T |    9 |   198 |    33   (4)  | 00:00:01  |
    -------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
      1- filter("MV_T"."TIME"＞TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss')AND"MV_T"."TIME"＜TO_DATE('2009-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss'))
    Note
    -----
      - dynamic sampling used for this statement

可以看到物化視圖的PCT特性在這里顯現(xiàn)了出來：雖然物化視圖和基表并不同步，但是由于DML語句所修改的分區(qū)并不是當前查詢涉及的分區(qū)，因此當前查詢訪問物化視圖仍然可以得到正確的結(jié)果，所以Oracle仍然選擇了使用物化視圖進行查詢重寫。

但是如果查詢使用了隱式類型轉(zhuǎn)換，則Oracle就不再使用查詢重寫功能：

    SQL＞ select time, count(＊)
      2  from t
      3  where time ＞ '01-1月 -09'
      4  and time ＜ '10-1月 -09'
      5  group by time;
    TIME           COUNT(＊)
    -------------- ----------
    04-1月 -09            1
    09-1月 -09            1
    01-1月 -09            1
    05-1月 -09            1
    03-1月 -09            1
    02-1月 -09            1
    08-1月 -09            1
    07-1月 -09            1
    06-1月 -09            1
    9 rows selected.
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2676183194
    -----------------------------------------------------------------------------------------
    |Id| Operation                   |Name |Rows | Bytes| Cost(%CPU)| Time    |Pstart|Pstop  |
    -----------------------------------------------------------------------------------------
    | 0| SELECT STATEMENT            |     |   20|   180 |    64   (2)| 00:00:01|       |       |
    | 1|  HASH GROUP BY              |     |   20|   180 |    64   (2)| 00:00:01|       |       |
    |＊2|   FILTER                    |     |     |       |           |         |       |       |
    | 3|    PARTITION RANGE ITERATOR |     |   20|   180 |    63   (0)| 00:00:01|KEY   |KEY    |
    |＊4|    TABLE ACCESS FULL       |T    |   20|   180 |    63   (0)| 00:00:01|KEY   |KEY    |
    -----------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
      2- filter(TO_DATE('01-1月 -09')＜TO_DATE('10-1月 -09'))
      4- filter("TIME"＞'01-1月 -09' AND "TIME"＜'10-1月 -09')
    Note
    -----
      - dynamic sampling used for this statement
    SQL＞ select /＊+ rewrite ＊/ time, count(＊)
      2  from t
      3  where time ＞ '01-1月 -09'
      4  and time ＜ '10-1月 -09'
      5  group by time;
    TIME              COUNT(＊)
    -------------- ----------
    04-1月 -09               1
    09-1月 -09               1
    01-1月 -09               1
    05-1月 -09               1
    03-1月 -09               1
    02-1月 -09               1
    08-1月 -09               1
    07-1月 -09 1
    06-1月 -09 1
    9 rows selected.
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2676183194
    -----------------------------------------------------------------------------------------
    |Id| Operation                   |Name|Rows|Bytes|Cost(%CPU)|Time    |Pstart  | Pstop|
    -----------------------------------------------------------------------------------------
    | 0| SELECT STATEMENT           |    |  20|   180|   64   (2)|00:00:01|        |       |
    | 1|  HASH GROUP BY              |    |  20|   180|   64   (2)|00:00:01|        |       |
    |＊2|   FILTER                    |    |    |      |          |         |        |       |
    | 3|    PARTITION RANGE ITERATOR|    |  20|   180|   63   (0)|00:00:01|   KEY  |   KEY |
    |＊4|    TABLE ACCESS FULL       |T   |  20|   180|   63   (0)|00:00:01|   KEY  |   KEY |
    -----------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
      2- filter(TO_DATE('01-1月 -09')＜TO_DATE('10-1月 -09'))
      4- filter("TIME"＞'01-1月 -09' AND "TIME"＜'10-1月 -09')

可以看出即使使用了REWRITE提示強制優(yōu)化器使用物化視圖進行查詢重寫，CBO仍然選擇了全表掃描的執(zhí)行計劃。其實道理很簡單：由于使用了隱式類型轉(zhuǎn)換，Oracle無法判斷當前查詢是否需要訪問被修改的分區(qū)，因此也就沒有辦法利用PCT的查詢重寫功能了。

通過AUTOTRACE給出的信息不難發(fā)現(xiàn)，Oracle甚至不知道隱式轉(zhuǎn)換后'01-1月-09'和'10-1月-09'這兩個日期的大小。為了確保SQL語句的正確性，CBO還增加了一個額外的過濾條件：filter(TO_DATE('01-1月 -09')＜TO_DATE('10-1月 -09'))。

小結(jié)

這又是一個隱式轉(zhuǎn)換給系統(tǒng)帶來危害的例子。雖然這個例子的危害還僅僅是性能方面，然而更多的由于隱式轉(zhuǎn)換最終導致錯誤的例子也是不勝枚舉的。雖然是老生常談，但是仍然要再一次重申：無論是SQL語句還是PL/SQL語句，都應該使用明確的顯式轉(zhuǎn)換，避免出現(xiàn)隱式轉(zhuǎn)換。