Oracle:物化视图的PCT特性(一)

Oracle的物化视图从9i开始支持了PARTITION CHANGE TRACKING（PCT）功能。本文简单描述一下PCT的概念及PCT的优点。

物化视图的PCT特性（二）：http://blog.itpub.net/post/468/21639

PCT是基于分区的修改跟踪，如果基表进行了分区，Oracle可以知道物化视图中的每条记录会被基表中的哪个或哪几个分区所影响。

PCT带来的优点主要体现在两个方面上：刷新和查询重新。

当基表发生DROP PARTITION或TRUNCATE PARTITION操作后，物化视图仍然可以执行快速刷新。而且即使不执行快速刷新，Oracle也不会将这个物化视图中所有记录的状态都设置为STALE，只有被分区操作影响的记录变为STALE，其他记录的状态仍然是FRESH，也就是说即使QUERY_REWRITE_INTEGRITY的值设置为ENFORCED或TRUSTED，这时的物化视图也可以部分的提供查询重新，只有那些受到分区影响的记录不再支持查询重新。

下面看个简单的例子：

SQL> conn yangtk/yangtk@test4
已连接。
SQL> show parameter query_rewrite

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

首先看一下运行的环境变量，允许查询重新，query_rewrite_integrity的值是enforced。

下面建立测试所需的例子：

SQL> create table t (id number, time date)
2 partition by range (time)
3 (partition p1 values less than (to_date('2004-1-1', 'yyyy-mm-dd')),
4 partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
5 partition p3 values less than (to_date('2006-1-1', 'yyyy-mm-dd')))
6 ;

表已创建。

SQL> insert into t select rownum, sysdate - rownum from dba_objects;

已创建6276行。

SQL> commit;

提交完成。

SQL> create materialized view log on t with rowid, sequence (id, time)
2 including new values;

实体化视图日志已创建。

SQL> create materialized view mv_t refresh fast enable query rewrite as
2 select time, count(*) from t group by time;

实体化视图已创建。

大致看一下数据的分布。

SQL> select count(*) from t partition(p1);

COUNT(*)
----------
5840

SQL> select count(*) from t partition(p2);

COUNT(*)
----------
366

SQL> select count(*) from t partition(p3);

COUNT(*)
----------
70

SQL> set autot on exp
SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')
2 and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME COUNT(*)
---------- ----------
01-1月 -05 1
02-1月 -05 1
03-1月 -05 1
04-1月 -05 1
05-1月 -05 1
06-1月 -05 1
07-1月 -05 1
08-1月 -05 1
09-1月 -05 1

已选择9行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=88)
1 0 TABLE ACCESS (FULL) OF 'MV_T' (Cost=3 Card=4 Bytes=88)

Oracle利用了查询重新机制来返回结果，下面drop掉一个分区，这个分区不影响刚才的那个查询的结果。

SQL> alter table t drop partition p1;

表已更改。

SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')
2 and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME COUNT(*)
---------- ----------
01-1月 -05 1
02-1月 -05 1
03-1月 -05 1
04-1月 -05 1
05-1月 -05 1
06-1月 -05 1
07-1月 -05 1
08-1月 -05 1
09-1月 -05 1

已选择9行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=22)
1 0 TABLE ACCESS (FULL) OF 'MV_T' (Cost=3 Card=1 Bytes=22)

可以看到，Oracle知道当前查询的数据不会被drop partition的操作所影响，因此仍然选择使用查询重新来返回结果。

SQL> exec dbms_mview.refresh('mv_t')

PL/SQL 过程已成功完成。

Drop分区后，物化视图仍然支持快速刷新。

SQL> alter table t truncate partition p3;

表已截掉。

SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')
2 and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

未选定行

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=22)
1 0 TABLE ACCESS (FULL) OF 'MV_T' (Cost=3 Card=1 Bytes=22)

这里的结果似乎有些奇怪，Oracle知道分区P3已经执行了TRUNCATE操作，而且我们查询的数据就是属于P3，为什么还使用查询重新功能呢。不过Oracle返回的结果是正确的。

SQL> select count(*) from mv_t;

COUNT(*)
----------
436

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'MV_T'

SQL> exec dbms_mview.refresh('mv_t')

PL/SQL 过程已成功完成。

SQL> select count(*) from mv_t;

COUNT(*)
----------
366

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'MV_T'

SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')
2 and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

未选定行

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=88)
1 0 TABLE ACCESS (FULL) OF 'MV_T' (Cost=3 Card=4 Bytes=88)

在快速刷新物化视图MV_T之前，物化视图上仍然保存着分区P3上的数据，虽然Oracle选择了查询重新，但是Oracle并不是仅仅根据MV_T上的信息而返回一个错误的答案，而是根据T执行了TRUNCATE PARTITION P3这个操作，而直接返回当前的查询结果“未选定行”。

PCT对于DML同样有效，但是如果查询的记录和DML修改的记录处于同一个分区中，则不会使用查询重新。

SQL> drop table t;

表已丢弃。

SQL> drop materialized view mv_t;

实体化视图已删除。

SQL> set autot off
SQL> create table t (id number, time date)
2 partition by range (time)
3 (partition p1 values less than (to_date('2004-1-1', 'yyyy-mm-dd')),
4 partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),
5 partition p3 values less than (to_date('2006-1-1', 'yyyy-mm-dd')))
6 ;

表已创建。

SQL> insert into t select rownum, sysdate - rownum from dba_objects;

已创建6276行。

SQL> commit;

提交完成。

SQL> create materialized view log on t with rowid, sequence (id, time)
2 including new values;

实体化视图日志已创建。

SQL> create materialized view mv_t refresh fast enable query rewrite as
2 select time, count(*) from t group by time;

实体化视图已创建。

SQL> set autot on exp
SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')
2 and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME COUNT(*)
---------- ----------
01-1月 -05 1
02-1月 -05 1
03-1月 -05 1
04-1月 -05 1
05-1月 -05 1
06-1月 -05 1
07-1月 -05 1
08-1月 -05 1
09-1月 -05 1

已选择9行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=88)
1 0 TABLE ACCESS (FULL) OF 'MV_T' (Cost=3 Card=4 Bytes=88)

SQL> delete t where time < to_date('2004-1-1', 'yyyy-mm-dd');

已删除5840行。

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=4 Card=82 Bytes=738)
1 0 DELETE OF 'T'
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=4 Card=82 Bytes=738)

SQL> commit;

提交完成。

SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')
2 and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME COUNT(*)
---------- ----------
01-1月 -05 1
02-1月 -05 1
03-1月 -05 1
04-1月 -05 1
05-1月 -05 1
06-1月 -05 1
07-1月 -05 1
08-1月 -05 1
09-1月 -05 1

已选择9行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=88)
1 0 TABLE ACCESS (FULL) OF 'MV_T' (Cost=3 Card=4 Bytes=88)

SQL> delete t where time > to_date('2005-1-11', 'yyyy-mm-dd');

已删除60行。

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=2 Card=20 Bytes=180)
1 0 DELETE OF 'T'
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=20 Bytes=180)

SQL> commit;

提交完成。

SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')
2 and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME COUNT(*)
---------- ----------
01-1月 -05 1
02-1月 -05 1
03-1月 -05 1
04-1月 -05 1
05-1月 -05 1
06-1月 -05 1
07-1月 -05 1
08-1月 -05 1
09-1月 -05 1

已选择9行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=9)
1 0 SORT (GROUP BY) (Cost=4 Card=1 Bytes=9)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=9)