创建Materialized view log时是否使用sequence clause的差别

创建物化视图日志的时候有一个选项是sequence，对于sequence的作用，Data Warehousing guide里有一段描述：
Oracle recommends that the keyword SEQUENCE  be included in your materialized 
view log statement unless you are sure that you will never perform a mixed DML 
operation (a combination of  INSERT ,  UPDATE , or  DELETE  operations on multiple 
tables


在利用物化视图日志对物化视图进行的一次增量刷新的过程中，如果物化视图基于多个master table而建立，且至少两个master table的MV log日志都有更新记录，那么这些mlog$_开头的日志表里就必须包含sequence$$字段，也就是说在MV log定义的时候要使用sequence选项，这样才能使物化视图能够正常刷新。
理解这句话的关键在于什么是"一次增量刷新"，增量刷新有两种："refresh fast on demand"和"refresh fast on commit"；对于"refresh fast on demand"一次增量刷新就是指执行一次dbms_mview.refresh将MV log里留存的内容读取出来更新到MV；对于"refresh fast on commit"一次增量更新就是在一个Transaction commit后将MV log里保存的对于master table的更改同步到MV。不管哪种方式当一次增量刷新的过程中发现两个以上的master table有了更改，就必须要求master table的MV log具有sequence$$字段，否则更新将无法同步到MV


/
// Create MV log的时候定义了sequence
/

###1、refresh on demand的情况
sqlplus hr/oracle
create table t0910_f1(stuid number constraint pk_stuid primary key,stuname varchar2(10)) tablespace ts0727;
create table t0910_d1(score number,stuid number constraint fk_stuid references t0910_f1(stuid),subject varchar2(10)) tablespace ts0727;
insert into t0910_f1 values(1,'stua');
insert into t0910_f1 values(2,'stub');
insert into t0910_f1 values(3,'stuc');


insert into t0910_d1 values(50,1,'maths');
insert into t0910_d1 values(60,2,'language');
insert into t0910_d1 values(80,1,'language');
insert into t0910_d1 values(40,2,'maths');
insert into t0910_d1 values(30,3,'language');
insert into t0910_d1 values(60,3,'maths');
commit;


create materialized view log on t0910_f1 tablespace ts0727 with rowid,sequence (stuid,stuname) including new values;
create materialized view log on t0910_d1 tablespace ts0727 with rowid,sequence (score,stuid,subject) including new values;

SQL> desc mlog$_t0910_f1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STUID                                              NUMBER
 STUNAME                                            VARCHAR2(10)
 M_ROW$$                                            VARCHAR2(255)
 SEQUENCE$$                                         NUMBER                <----创建了MV log时使用了sequence后就有了SEQUENCE$$列
 SNAPTIME$$                                         DATE
 DMLTYPE$$                                          VARCHAR2(1)
 OLD_NEW$$                                          VARCHAR2(1)
 CHANGE_VECTOR$$                                    RAW(255)
 XID$$                                              NUMBER


create materialized view mv0910_1 build immediate refresh fast with rowid enable query rewrite as select f.stuname,sum(score),avg(score),count(score),count(*) from t0910_d1 d,t0910_f1 f where f.stuid=d.stuid group by f.stuname;


update t0910_f1 set stuname='stuaa' where stuname='stua';
commit;


update t0910_d1 set score=score-10 where stuid=1;
commit;


SQL> select count(*) from mlog$_t0910_f1;


  COUNT(*)
----------
         2


SQL> select count(*) from mlog$_t0910_d1;


  COUNT(*)
----------
         4


exec dbms_mview.refresh('hr.mv0910_1');


SQL> select * from hr.mv0910_1;


STUNAME    SUM(SCORE) AVG(SCORE) COUNT(SCORE)   COUNT(*)
---------- ---------- ---------- ------------ ----------
stub              100         50            2          2
stuc               90         45            2          2
stuaa             110         55            2          2


###2、refresh on commit的场景
。。。略去，结果同上，MV能够正常更新


/
// Create MV log的时候未定义sequence
/

###1、refresh on demand的情况
drop table t0910_d1;
drop table t0910_f1;
drop materialized view mv0910_1;


create table t0910_f1(stuid number constraint pk_stuid primary key,stuname varchar2(10)) tablespace ts0727;
create table t0910_d1(score number,stuid number constraint fk_stuid references t0910_f1(stuid),subject varchar2(10)) tablespace ts0727;
insert into t0910_f1 values(1,'stua');
insert into t0910_f1 values(2,'stub');
insert into t0910_f1 values(3,'stuc');


insert into t0910_d1 values(50,1,'maths');
insert into t0910_d1 values(60,2,'language');
insert into t0910_d1 values(80,1,'language');
insert into t0910_d1 values(40,2,'maths');
insert into t0910_d1 values(30,3,'language');
insert into t0910_d1 values(60,3,'maths');
commit;


create materialized view log on t0910_f1 tablespace ts0727 with rowid (stuid,stuname) including new values;
create materialized view log on t0910_d1 tablespace ts0727 with rowid (score,stuid,subject) including new values;


create materialized view mv0910_1 build immediate refresh fast with rowid enable query rewrite as select f.stuname,sum(score),avg(score),count(score),count(*) from t0910_d1 d,t0910_f1 f where f.stuid=d.stuid group by f.stuname;




SQL> select * from mv0910_1;


STUNAME    SUM(SCORE) AVG(SCORE) COUNT(SCORE)   COUNT(*)
---------- ---------- ---------- ------------ ----------
stub              100         50            2          2
stua              130         65            2          2
stuc               90         45            2          2


update t0910_f1 set stuname='stuaa' where stuname='stua';   <---这两个update尽管在不同的transaction里提交，也会报"ORA-32316"错误，因为是refresh on demand
commit;


update t0910_d1 set score=score-10 where stuid=1;
commit;


SQL> select count(*) from mlog$_t0910_f1;


  COUNT(*)
----------
         2


SQL> select count(*) from mlog$_t0910_d1;


  COUNT(*)
----------
         4


SQL> exec dbms_mview.refresh('hr.mv0910_1');
BEGIN dbms_mview.refresh('hr.mv0910_1'); END;


*
ERROR at line 1:
ORA-32316: REFRESH FAST of "HR"."MV0910_1" unsupported after mixed DML
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1


SQL> select * from hr.mv0910_1;


STUNAME    SUM(SCORE) AVG(SCORE) COUNT(SCORE)   COUNT(*)
---------- ---------- ---------- ------------ ----------
stub              100         50            2          2
stua              130         65            2          2
stuc               90         45            2          2


SQL> select count(*) from mlog$_t0910_f1;


  COUNT(*)
----------
         2


SQL> select count(*) from mlog$_t0910_d1;


  COUNT(*)
----------
         4


exec dbms_mview.refresh('hr.mv0910_1');


SQL> col msgtxt format a50
SQL> col mvname format a8
SQL> col capability_name format a29
SQL> col mvowner format a5
SQL> set linesize 150 pagesize 100


exec dbms_mview.explain_mview('hr.mv0910_1','chh1');           <----使用explain_mview结果明确指出因mv log没有sequence，而无法支持multi-table下除insert外的其它DML语句，仅支持在one-table下的ANY DML更新以及对于multi-tables的Insert语句


select mvowner,mvname, capability_name,possible,msgtxt from hr.mv_capabilities_table where possible='N'


MVOWN MVNAME   CAPABILITY_NAME      P MSGTXT
----- -------- -------------------- - --------------------------------------------------
HR    MV0910_1 PCT                  N
HR    MV0910_1 PCT_TABLE            N relation is not a partitioned table
HR    MV0910_1 PCT_TABLE            N relation is not a partitioned table
HR    MV0910_1 REFRESH_FAST_AFTER_A N mv log does not have sequence #         <--- MV log上没有sequence
               NY_DML
               
HR    MV0910_1 REFRESH_FAST_AFTER_A N mv log does not have sequence #         <--- MV log上没有sequence
               NY_DML


HR    MV0910_1 REFRESH_FAST_PCT     N PCT is not possible on any of the detail tables in
                                       the materialized view
HR    MV0910_1 REWRITE_PCT          N general rewrite is not possible or PCT is not poss
                                      ible on any of the detail tables


HR    MV0910_1 PCT_TABLE_REWRITE    N relation is not a partitioned table
HR    MV0910_1 PCT_TABLE_REWRITE    N relation is not a partitioned table

###2、refresh on commit的情况
drop table t0910_d1;
drop table t0910_f1;
drop materialized view mv0910_1;

create table t0910_f1(stuid number constraint pk_stuid primary key,stuname varchar2(10)) tablespace ts0727;
create table t0910_d1(score number,stuid number constraint fk_stuid references t0910_f1(stuid),subject varchar2(10)) tablespace ts0727;
insert into t0910_f1 values(1,'stua');
insert into t0910_f1 values(2,'stub');
insert into t0910_f1 values(3,'stuc');

insert into t0910_d1 values(50,1,'maths');
insert into t0910_d1 values(60,2,'language');
insert into t0910_d1 values(80,1,'language');
insert into t0910_d1 values(40,2,'maths');
insert into t0910_d1 values(30,3,'language');
insert into t0910_d1 values(60,3,'maths');
commit;


create materialized view log on t0910_f1 tablespace ts0727 with rowid (stuid,stuname) including new values;
create materialized view log on t0910_d1 tablespace ts0727 with rowid (score,stuid,subject) including new values;


create materialized view mv0910_1 build immediate refresh fast on commit with rowid enable query rewrite as select f.stuname,sum(score),avg(score),count(score),count(*) from t0910_d1 d,t0910_f1 f where f.stuid=d.stuid group by f.stuname;




SQL> select * from mv0910_1;


STUNAME    SUM(SCORE) AVG(SCORE) COUNT(SCORE)   COUNT(*)
---------- ---------- ---------- ------------ ----------
stua              130         65            2          2
stub              100         50            2          2
stuc               90         45            2          2


update t0910_f1 set stuname='stuaa' where stuname='stua';   <---当这两个update在不同的transaction里提交，一切正常
commit;


update t0910_d1 set score=score-10 where stuid=1;
commit;


SQL> select count(*) from mlog$_t0910_f1;


  COUNT(*)
----------
         0


SQL> select count(*) from mlog$_t0910_d1;


  COUNT(*)
----------
         0


SQL> select * from hr.mv0910_1;


STUNAME    SUM(SCORE) AVG(SCORE) COUNT(SCORE)   COUNT(*)
---------- ---------- ---------- ------------ ----------
stub              100         50            2          2
stuc               90         45            2          2
stuaa             110         55            2          2


update t0910_f1 set stuname='stua' where stuname='stuaa';   <---当这两个update在同一个transaction里提交，就有问题了


update t0910_d1 set score=score-10 where stuid=1;


SQL> select count(*) from mlog$_t0910_f1;


  COUNT(*)
----------
         2


SQL> select count(*) from mlog$_t0910_d1;


  COUNT(*)
----------
         4


SQL> commit;          <---虽然提交时没有报错


Commit complete.


SQL> select count(*) from mlog$_t0910_f1;   <---MV log表的内容已经消失了


  COUNT(*)
----------
         0


SQL> select count(*) from mlog$_t0910_d1;   <---MV log表的内容已经消失了


  COUNT(*)
----------
         0


SQL> select * from mv0910_1;            <---但是MV本身却没有更新


STUNAME    SUM(SCORE) AVG(SCORE) COUNT(SCORE)   COUNT(*)
---------- ---------- ---------- ------------ ----------
stuaa             110         55            2          2
stub              100         50            2          2
stuc               90         45            2          2


***alert.log里可以看到一行提示表名本次增量刷新没有成功，因为MV log已经被清除，只能使用complete refresh解决：
Fri Sep 11 15:29:39 2015
Following on-commit snapshots not refreshed :
NEWUSER.MV0910_1 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1797342/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1797342/