建立物化檢視日誌的時候有一個選項是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

建立Materialized view log時是否使用sequence clause的差別

相關文章