上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
隐式转换影响物化视图查询重写
有人提出过关于一个物化视图无法查询重写的问题。不过通过诊断最终发现，问题其实并不是物化视图的功能所导致的，真正的原因是由于引入了隐式转换。
关于物化视图的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.
首先建立物化视图的基表，插入一些测试数据，之后建立物化视图日志和快速刷新且支持查询重写的物化视图，然后检查这个物化视图当前是否支持查询重写：
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
由于物化视图支持查询重写，而且物化视图的数据是最新的，Oracle的CBO在分析后认为查询物化视图的代价要比直接查询基表的代价低，因此选择了查询物化视图。
对基表进行DML操作，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"＜TO_DATE('2009-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement
可以看到物化视图的PCT特性在这里显现了出来：虽然物化视图和基表并不同步，但是由于DML语句所修改的分区并不是当前查询涉及的分区，因此当前查询访问物化视图仍然可以得到正确的结果，所以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')＜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提示强制优化器使用物化视图进行查询重写，CBO仍然选择了全表扫描的执行计划。其实道理很简单：由于使用了隐式类型转换，Oracle无法判断当前查询是否需要访问被修改的分区，因此也就没有办法利用PCT的查询重写功能了。
通过AUTOTRACE给出的信息不难发现，Oracle甚至不知道隐式转换后'01-1月-09'和'10-1月-09'这两个日期的大小。为了确保SQL语句的正确性，CBO还增加了一个额外的过滤条件：filter(TO_DATE('01-1月 -09')＜TO_DATE('10-1月 -09'))。
小结
这又是一个隐式转换给系统带来危害的例子。虽然这个例子的危害还仅仅是性能方面，然而更多的由于隐式转换最终导致错误的例子也是不胜枚举的。虽然是老生常谈，但是仍然要再一次重申：无论是SQL语句还是PL/SQL语句，都应该使用明确的显式转换，避免出现隐式转换。