隱式轉換影響物化檢視查詢重寫

yangtingkun發表於2010-06-03
原文網址 : http://blog.itpub.net/4227/viewspace-664338/

今天有人問我一個物化檢視查詢重寫的問題，最後發現問題其實和物化檢視的功能沒有多大的關係，而是隱式轉換導致的問題。

論壇上的問題出處：http://www.itpub.net/thread-1310260-1-2.html

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.

下面看看物化檢視是否可以查詢重寫：

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"              hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

下面對基表進行DML操作，但是這個操作的結果並不會影響當前查詢的分割槽：

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"              hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

可以看到，物化檢視的PCT特性在這裡顯現出來，雖然物化檢視和基表並不同步，但是由於Oracle進行修改分割槽並不是當前查詢的分割槽，因此查詢的資料在物化檢視中仍然可以正確的得到，所以Oracle仍然選擇了物化檢視進行查詢重寫。

但是如果和上面連結的帖子的貼主一樣使用隱式型別轉換，則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')月 -09'))
   4 - filter("TIME">'01-1月 -09' AND "TIME"月 -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')月 -09'))
   4 - filter("TIME">'01-1月 -09' AND "TIME"月 -09')

Note
-----
   - dynamic sampling used for this statement

可以看到，即使是使用REWRITE提示強制使用物化檢視進行查詢重寫，Oracle仍然選擇了表掃描。

其實道理很簡單，由於使用了隱式型別轉換，Oracle並不知道當前的查詢是否需要訪問被修改的分割槽，也就沒有辦法利用PCT的查詢重寫功能了。

根據Oracle的給出的資訊發現，Oracle甚至不知道隱式轉換後’01-1月 -09'和'10-1月 -09'的大小，為了保證SQL的正確性，還增加了過濾條件：filter(TO_DATE('01-1月 -09')月 -09'))。

又是一個說明隱式轉換危害的例子，在寫SQL的時候應該避免使用隱式轉換，對當前的情況而言，隱式轉換並不會造成任何的誤解，但是可能會引發其他的問題。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-664338/，如需轉載，請註明出處，否則將追究法律責任。

相關文章