物化视图的PCT特性（一）

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

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

ITPUB个人空间I(ZO8LpQ7W

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

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

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

下面看个简单的例子：

SQL> conn yangtk/yangtk@test4ITPUB个人空间&v:e cqI
已连接。
4\@\F9RC Vdg0SQL> show parameter query_rewrite

NAME                                 TYPE        VALUE
8}^DB%L`i0------------------------------------ ----------- ---------------------
V9~%\J U0query_rewrite_enabled                string      TRUEITPUB个人空间iV~&s JOn
query_rewrite_integrity              string      enforced

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

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

SQL> create table t (id number, time date)
E7mj I9Z^/?I0  2  partition by range (time)
/Nv4z B-K6x4?%v,M0  3  (partition p1 values less than (to_date('2004-1-1', 'yyyy-mm-dd')),
a7n*osN0].C0  4  partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')), ITPUB个人空间x n yA/[;Z
  5  partition p3 values less than (to_date('2006-1-1', 'yyyy-mm-dd')))
ba ^V)}{8G0  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) ITPUB个人空间 a5@p!gu y"X'j
  2  including new values;

实体化视图日志已创建。

SQL> create materialized view mv_t refresh fast enable query rewrite as ITPUB个人空间9\0Ky)@6R.r6@gJ
  2  select time, count(*) from t group by time;

实体化视图已创建。

大致看一下数据的分布。

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

  COUNT(*)

k bQ"N.DvMA0----------ITPUB个人空间q YA)v:q-@
      5840

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

  COUNT(*)
N
E1v1yf$^0----------
k6zw"pSpD4h0       366

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

  COUNT(*)
8c fXt;E0----------
g5wR7E0^0        70

SQL> set autot on expITPUB个人空间s%N)_?4nq;kJ'i
SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')
Jpe+K{*n7K? AuW0  2  and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME         COUNT(*)ITPUB个人空间2J?Ark_(`:|!_
---------- ----------ITPUB个人空间Ww
Q m E/rJ V3R"A g
01-1月 -05          1
"_Y-|`%mWa$KIk002-1月 -05          1ITPUB个人空间o
B k(O-e.rv#C%X
03-1月 -05          1ITPUB个人空间
Y#ussr:Y
04-1月 -05          1ITPUB个人空间6r'v"^ h*A}'ij
05-1月 -05          1
nZ-YH-v:A.D/U#K006-1月 -05          1
L;v?9moQy
H9h-]007-1月 -05          1ITPUB个人空间&uzbl&p1y
Z:JML
08-1月 -05          1
c5~V#H1q
_wq009-1月 -05          1

已选择9行。

{ vS#Q\!o|0Execution PlanITPUB个人空间*s5} sC/MF#i,X
----------------------------------------------------------ITPUB个人空间3fzr.o K N
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=88)
+y:CE+}jv0   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')ITPUB个人空间Zrb.V7@)wD3`
  2  and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME         COUNT(*)
b H_@l5Q`R,Xf0---------- ----------ITPUB个人空间p$xf&~JC3U6@/a_"L
01-1月 -05          1
6R7W"b8Eg-I002-1月 -05          1
]aU2V7X(?$M5[003-1月 -05          1
)m|p)uJ:ex0q)Ep"j004-1月 -05          1ITPUB个人空间:d+d w"X5@9c$W%\M
05-1月 -05          1
2^R6G_g X006-1月 -05          1ITPUB个人空间8UaDWg
07-1月 -05          1
OLR^&X UQ\008-1月 -05          1ITPUB个人空间2k]&x6N7]ZL
C,_
09-1月 -05          1

已选择9行。

ITPUB个人空间"s l0g%K`kg0I
Execution Plan
7^Vv+E6B/bu(u0----------------------------------------------------------ITPUB个人空间!^$IL fP
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=22)ITPUB个人空间:t%_/JbzR9N@m
   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')
#I+m3p wq.ol+R&i1NP.G0  2  and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

未选定行

ITPUB个人空间S m/{R&JszcO
Execution PlanITPUB个人空间?ul5YJ.a!x"w
----------------------------------------------------------ITPUB个人空间l o,^-b3Q
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=22)ITPUB个人空间0I%J8On)v$nwJ
   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(*)ITPUB个人空间0C6\nx3IC&^3R [
----------
e&i{Acf)OH"}(]0       436

9eN,_/`&xL0Execution PlanITPUB个人空间+T8pL4k'ku?uv
----------------------------------------------------------ITPUB个人空间8I@7g'd)mP#U\o^
   0      SELECT STATEMENT Optimizer=CHOOSE
R { J7Z!z0]npjJ0   1    0   SORT (AGGREGATE)
6n){)v0Y]hy4@0   2    1     TABLE ACCESS (FULL) OF 'MV_T'

SQL> exec dbms_mview.refresh('mv_t')

PL/SQL 过程已成功完成。

SQL> select count(*) from mv_t;

  COUNT(*)
?"m%r.B$](D0----------
-Lq^9l\IY V0       366

ITPUB个人空间$T(k i5D.f,Y
Execution Plan
!k['W.|.F"y^0----------------------------------------------------------
a0a1b
JQf*@0   0      SELECT STATEMENT Optimizer=CHOOSEITPUB个人空间3xm yj:Et
   1    0   SORT (AGGREGATE)
%a%If6a7v0   2    1     TABLE ACCESS (FULL) OF 'MV_T'

SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')
xL`v!V4T&G0  2  and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

未选定行

ITPUB个人空间?DYBe
Execution PlanITPUB个人空间#O^\ fX
----------------------------------------------------------
5pV N%hPR!o!x0   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=88)ITPUB个人空间5by k)]e
   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 offITPUB个人空间 g%vg1Z6L6rW2o:n
SQL> create table t (id number, time date)
,QYjB3XF)y4b.F0  2  partition by range (time)
$p9sR{DQe[}0  3  (partition p1 values less than (to_date('2004-1-1', 'yyyy-mm-dd')), ITPUB个人空间hl!U(zUe I3X\
  4  partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')), ITPUB个人空间z
mL9O+V}${
  5  partition p3 values less than (to_date('2006-1-1', 'yyyy-mm-dd')))ITPUB个人空间(\[
[.dF+r3G!Gq2u
  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)
4DO#De)ft0  2  including new values;

实体化视图日志已创建。

SQL> create materialized view mv_t refresh fast enable query rewrite as
'@1ubDf-BV$o-}0  2  select time, count(*) from t group by time;

实体化视图已创建。

SQL> set autot on expITPUB个人空间 r;^:|fZb f
SQL> select time, count(*) from t where time > to_date('2005-1-1', 'yyyy-mm-dd')ITPUB个人空间"p$Pp3L y b
  2  and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME         COUNT(*)ITPUB个人空间o ~P*k9o
---------- ----------ITPUB个人空间i ^-FJ9Ptx
01-1月 -05          1
ku;I a^[ V&F#U+I002-1月 -05          1ITPUB个人空间s&d@
T$f(V*Y2{
03-1月 -05          1ITPUB个人空间J#w:@'~d:Rn't(}
04-1月 -05          1ITPUB个人空间e/MXC)RqZ6Q oT
05-1月 -05          1ITPUB个人空间4E9Hct4b3UX\P
06-1月 -05          1ITPUB个人空间.t&]+IU j4V
07-1月 -05          1
D,m-J-|4Fr008-1月 -05          1
/B%Z j9Gb4W009-1月 -05          1

已选择9行。

IR|Y-dsX0Execution Plan
CP7_
F?E0----------------------------------------------------------
].J\[_4`0   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=88)
!d-dm`3qL-H0   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行。

ITPUB个人空间_p)Ii8dFc
Execution Plan
4sOk wk.S\-R3v2AI5y0----------------------------------------------------------
1HN:w~:P,o(C0   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=4 Card=82 Bytes=738)
K.p0t8WP-new^0   1    0   DELETE OF 'T'
Q(y,hYb i0   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')
J/q0LG7c&wM+C0  2  and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME         COUNT(*)
F"o${)[ d2]&qpk0---------- ----------ITPUB个人空间-m*^*F k7Iz
01-1月 -05          1ITPUB个人空间UYy1E,WPS
02-1月 -05          1ITPUB个人空间Tv1]2\ mG}q6?
03-1月 -05          1
zM"R)Tl'\:Cm J004-1月 -05          1
wl6E1} q:O005-1月 -05          1ITPUB个人空间#YPBylnQ
06-1月 -05          1ITPUB个人空间#Rl+u:zT4`6M|
07-1月 -05          1
%MUm(vL{008-1月 -05          1ITPUB个人空间m(v/l:F+Z
09-1月 -05          1

已选择9行。

3kS2G'l,h DW&q_0Execution PlanITPUB个人空间OYZ5uJ(Y
----------------------------------------------------------ITPUB个人空间f5c%O.W O+d&L
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=88)ITPUB个人空间"X7g&[2Z"V)Ml$Q"j0V
   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行。

ITPUB个人空间"Yd/f [.`
Execution PlanITPUB个人空间#|rul?
----------------------------------------------------------ITPUB个人空间 gO
UT |,I].D0c4F
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=2 Card=20 Bytes=180)ITPUB个人空间 d`"l JC,E~
   1    0   DELETE OF 'T'ITPUB个人空间(?:L^[,a5pmh
   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')ITPUB个人空间:ATq-v L6w
  2  and time < to_date('2005-1-10', 'yyyy-mm-dd') group by time;

TIME         COUNT(*)
}1C!Av#dc2\"Q0---------- ----------ITPUB个人空间0T"b~tJ n
01-1月 -05          1ITPUB个人空间G!v*Qv"g6YrMN2D
02-1月 -05          1ITPUB个人空间K8n.zt1T{@VK
03-1月 -05          1ITPUB个人空间
z8\a8] f}`%S
04-1月 -05          1ITPUB个人空间.D9qDX,^PJ.YP
05-1月 -05          1ITPUB个人空间|:Cfs7G\PlP(B
06-1月 -05          1ITPUB个人空间 x/M"F0b4{S N7H6IO
07-1月 -05          1ITPUB个人空间1u E2|:b#M!~z|
08-1月 -05          1
-TVm`V009-1月 -05          1

已选择9行。

5l
}l$rg0Execution PlanITPUB个人空间*}(|9Vw,S*iW
----------------------------------------------------------
Wf%R5tMj0   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=9)
GnfzVZd"S0   1    0   SORT (GROUP BY) (Cost=4 Card=1 Bytes=9)ITPUB个人空间3VpK%u8w:I&p|b
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=9)