隐式转换影响物化视图查询重写
今天有人问我一个物化视图查询重写的问题，最后发现问题其实和物化视图的功能没有多大的关系，而是隐式转换导致的问题。
论坛上的问题出处：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"
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"
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')
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')
4 - filter("TIME">'01-1月 -09' AND "TIME"<'10-1月 -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')
又是一个说明隐式转换危害的例子，在写SQL的时候应该避免使用隐式转换，对当前的情况而言，隐式转换并不会造成任何的误解，但是可能会引发其他的问题。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-664338/ ，如需转载，请注明出处，否则将追究法律责任。
