物化視圖幾個(gè)知識(shí)點(diǎn)
?
源表：物化視圖數(shù)據(jù)源對(duì)應(yīng)的表
基表：物化視圖對(duì)應(yīng)的表
本文主要內(nèi)容包括：
1、如何使源表的數(shù)據(jù)變化不影響物化視圖的快速刷新
2、建好物化視圖后，當(dāng)基表或者源表的結(jié)構(gòu)發(fā)生變化對(duì)物化視圖刷新的影響。
測(cè)試數(shù)據(jù)準(zhǔn)備：
suk@ORA9I> CREATE TABLE T_MV(C1 NUMBER,C2 NUMBER,PRIMARY KEY(C1));
Table created.
suk@ORA9I> CREATE MATERIALIZED VIEW LOG ON T_MV;
Materialized view log created.
--準(zhǔn)備4種方法測(cè)試的MV
suk@ORA9I> CREATE MATERIALIZED VIEW MV_1 REFRESH FAST AS SELECT C1,C2 FROM T_MV;
Materialized view created.
suk@ORA9I> CREATE MATERIALIZED VIEW MV_2 REFRESH FAST AS SELECT * FROM T_MV;
Materialized view created.
suk@ORA9I> CREATE MATERIALIZED VIEW MV_3 REFRESH FAST AS SELECT * FROM T_MV T;
Materialized view created.
suk@ORA9I> CREATE MATERIALIZED VIEW MV_4 REFRESH FAST AS SELECT T.* FROM T_MV T;
Materialized view created.
?
一、如何修改源表數(shù)據(jù)，而不會(huì)產(chǎn)生MLOG$_XXX
物化視圖在快速刷新時(shí)是根據(jù)MLOG$_XXX的記錄來(lái)決定那些數(shù)據(jù)需要刷新的，所以，如果想要源表修改的數(shù)據(jù)不被刷新的話，就需要把MLOG$_XXX對(duì)應(yīng)的記錄去掉。
?
1、用函數(shù)包
?
suk@ORA9I> INSERT INTO T_MV VALUES(1,1);
1 row created.
suk@ORA9I> SELECT * FROM MLOG$_T_MV;
C1 ??????? SNAPTIME$ D O CHANGE_VECTO
---------- --------- - - ------------
1 ???????? 01-JAN-00 I N FE
suk@ORA9I> ROLLBACK;
Rollback complete.
suk@ORA9I> EXEC DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION('SUK','T_MV');
--用這個(gè)過(guò)程可以使得對(duì)源表的DML操作不產(chǎn)生MLOG，影響范圍是從BEGIN_TABLE_REORGANIZATION到END_TABLE_REORGANIZATION其間
PL/SQL procedure successfully completed.
suk@ORA9I> COMMIT;--一定要執(zhí)行commit，否則還會(huì)產(chǎn)生MLOG$
Commit complete.
suk@ORA9I> INSERT INTO T_MV VALUES(1,1);
1 row created.
suk@ORA9I> SELECT COUNT(1) FROM MLOG$_T_MV;
COUNT(1)
----------
0
suk@ORA9I> ROLLBACK;
Rollback complete.
suk@ORA9I> EXEC DBMS_MVIEW.END_TABLE_REORGANIZATION('SUK','T_MV');
PL/SQL procedure successfully completed.
--注意：對(duì)于表的DML非常頻繁，如果只是想讓某小部分?jǐn)?shù)據(jù)不產(chǎn)生日志，則這種方法不適合。可以用第二種方法。
2、刪除MLOG$記錄
第二種方法很直接，就是直接刪除不想被刷新的數(shù)據(jù)對(duì)應(yīng)的修改日志。
這種方法的難點(diǎn)是如何準(zhǔn)確找出那些是你需要?jiǎng)h除的日志。方法很麻煩，這里不詳細(xì)說(shuō)了。
?
二、如何修改物化視圖數(shù)據(jù)時(shí)，不產(chǎn)生USLOG_XXX
這種情況只能用手工刪除USLOG$_XXX的方法了。
?
三、源表結(jié)構(gòu)發(fā)生變化時(shí)
1、源表添加字段
--添加字段
suk@ORA9I> ALTER TABLE T_MV ADD(COL3 NUMBER);
Table altered.
--對(duì)mv_1進(jìn)行兩種方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','COMPELETE');
PL/SQL procedure successfully completed.
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','FAST');
PL/SQL procedure successfully completed.
--對(duì)mv_2進(jìn)行兩種方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_2','COMPELETE');
PL/SQL procedure successfully completed.
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_2','FAST');
PL/SQL procedure successfully completed.
--對(duì)mv_3進(jìn)行兩種方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_3','COMPELETE');
PL/SQL procedure successfully completed.
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_3','FAST');
PL/SQL procedure successfully completed.
--對(duì)mv_4進(jìn)行兩種方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','COMPELETE');
BEGIN DBMS_MVIEW.REFRESH('MV_4','COMPELETE'); END;
*
ERROR at line 1:
ORA-12018: following error encountered during code generation for "SUK"."MV_4"
ORA-00904: "COL3": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','FAST');
PL/SQL procedure successfully completed.
--從以上測(cè)試結(jié)果可以看出，源表添加字段時(shí)，只有MV_4在完全刷新時(shí)會(huì)出錯(cuò)。為什么會(huì)這樣呢？
--先看看MV的DDL：
suk@ORA9I> SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_1','SUK') FROM DUAL;
CREATE MATERIALIZED VIEW "SUK"."MV_1"
......
AS SELECT C1,C2 FROM T_MV
suk@ORA9I> C/1/2
1* SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_2','SUK') FROM DUAL
suk@ORA9I> /
CREATE MATERIALIZED VIEW "SUK"."MV_2"
......
AS SELECT "T_MV"."C1" "C1","T_MV"."C2" "C2" FROM "T_MV" "T_MV"
suk@ORA9I> C/2/3
1* SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_3','SUK') FROM DUAL
suk@ORA9I> /
CREATE MATERIALIZED VIEW "SUK"."MV_3"
......
AS SELECT "T"."C1" "C1","T"."C2" "C2" FROM "T_MV" "T"
suk@ORA9I> C/3/4
1* SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_4','SUK') FROM DUAL
suk@ORA9I> /
CREATE MATERIALIZED VIEW "SUK"."MV_4"
......
AS SELECT T.* FROM T_MV T
--看每一個(gè)MV的DDL的最后一行，不難發(fā)現(xiàn)問題了。
--在前三種情況下，oracle在創(chuàng)建MV時(shí)會(huì)翻譯成當(dāng)前源表對(duì)應(yīng)的字段名;但第四種情況則不然，它是在刷新時(shí)才翻譯成源表對(duì)應(yīng)的字段，如果源表的結(jié)構(gòu)發(fā)生變化，那很明顯，MV刷新會(huì)出現(xiàn)問題。
--那MV_4為什么快速刷新就不會(huì)出錯(cuò)呢？通過(guò)trace文件，可以看出完全刷新和快速刷新的不同之處：
--完全刷新
INSERT INTO "SUK"."MV_4"("C1","C2","COL3") SELECT "T"."C1","T"."C2","T"."COL3" FROM "T_MV" "T"
--快速刷新
INSERT INTO "SUK"."MV_4" ("C1","C2")
VALUES
(:1,:2)
--可以看出，完全刷新時(shí)，是根據(jù)源表的結(jié)構(gòu)進(jìn)行刷新的
--快速刷新時(shí)，是根據(jù)MV的結(jié)構(gòu)進(jìn)行刷新的
2、新添加的字段數(shù)據(jù)發(fā)生變化，快速刷新是否會(huì)刷新該記錄
suk@ORA9I> SELECT * FROM T_MV;
C1 ??????? C2 ??????? COL3
---------- ---------- ----------
1 ???????? 3 ???????? 3
suk@ORA9I> UPDATE T_MV SET COL3=4;
1 row updated.
suk@ORA9I> COMMIT;
Commit complete.
suk@ORA9I> @begin_trace
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','FAST');
suk@ORA9I> @end_trace
--從trace文件中可以發(fā)現(xiàn)如下語(yǔ)句：
UPDATE "SUK"."MV_1" SET "C1" = :1,"C2" = :2
WHERE
"C1" = :1
--說(shuō)明在源表中且在MV不存在的字段的數(shù)值發(fā)生變化，MV也會(huì)刷新這條數(shù)據(jù)。且MV的刷新方式是把整條記錄的所有字段都更新
3、源表刪除字段
suk@ORA9I> ALTER TABLE T_MV DROP COLUMN COL3;
Table altered.
suk@ORA9I> ALTER TABLE T_MV DROP COLUMN C2;
Table altered.
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','COMPELETE');
BEGIN DBMS_MVIEW.REFRESH('MV_1','COMPELETE'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00904: "T_MV"."C2": invalid identifier
ORA-00904: "C2": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','FAST');
BEGIN DBMS_MVIEW.REFRESH('MV_1','FAST'); END;
*
ERROR at line 1:
ORA-12057: materialized view "SUK"."MV_1" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
.....MV_1到MV_3都包同樣的錯(cuò)誤.....
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','COMPELETE');
PL/SQL procedure successfully completed.
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','FAST');
PL/SQL procedure successfully completed.
--其實(shí)這個(gè)結(jié)果可以根據(jù)上一步推斷出來(lái)了，現(xiàn)在用試驗(yàn)也證明了。
--根本原因就是建立MV的DDL不同，也就是是否指定*導(dǎo)致的
四、基表結(jié)構(gòu)發(fā)生變化時(shí)
剛才討論了源表的結(jié)構(gòu)變化對(duì)MV刷新的影響，下面討論基表的結(jié)構(gòu)對(duì)MV刷新的影響。
?
這個(gè)問題相對(duì)簡(jiǎn)單一點(diǎn)，我們知道，修改基表不會(huì)對(duì)MV建立的DDL造成影響，也就是不會(huì)改變MV的刷新語(yǔ)句，所以，很容易得到以下結(jié)論：
1、如果基表添加字段，則不會(huì)影響快速刷新和完全刷新
2、如果基表刪除字段，則不能快速刷新和完全刷新
suk@ORA9I> CREATE TABLE T_MV(C1 NUMBER,C2 NUMBER,PRIMARY KEY(C1));
Table created.
suk@ORA9I> CREATE MATERIALIZED VIEW LOG ON T_MV;
Materialized view log created.
suk@ORA9I> CREATE MATERIALIZED VIEW MV_1 REFRESH FAST AS SELECT C1,C2 FROM T_MV;
Materialized view created.
suk@ORA9I> CREATE MATERIALIZED VIEW MV_4 REFRESH FAST AS SELECT T.* FROM T_MV T;
Materialized view created.
--添加基表字段
suk@ORA9I> ALTER TABLE MV_1 ADD (C3 NUMBER);
Table altered.
suk@ORA9I> ALTER TABLE MV_4 ADD (C3 NUMBER);
Table altered.
----對(duì)mv_1進(jìn)行兩種方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','COMPELETE');
PL/SQL procedure successfully completed.
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','FAST');
PL/SQL procedure successfully completed.
--對(duì)mv_4進(jìn)行兩種方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','COMPELETE');
PL/SQL procedure successfully completed.
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','FAST');
PL/SQL procedure successfully completed.
--刪除基表字段
--刪除字段
suk@ORA9I> ALTER TABLE MV_1 DROP COLUMN C3;
Table altered.
suk@ORA9I> ALTER TABLE MV_1 DROP COLUMN C2;
Table altered.
suk@ORA9I> ALTER TABLE MV_4 DROP COLUMN C3;
Table altered.
suk@ORA9I> ALTER TABLE MV_4 DROP COLUMN C2;
Table altered.
--對(duì)mv_1進(jìn)行兩種方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','COMPELETE');
BEGIN DBMS_MVIEW.REFRESH('MV_1','COMPELETE'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00904: "C2": invalid identifier
ORA-00904: "C2": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_1','FAST');
BEGIN DBMS_MVIEW.REFRESH('MV_1','FAST'); END;
*
ERROR at line 1:
ORA-12057: materialized view "SUK"."MV_1" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
--對(duì)mv_4進(jìn)行兩種方法刷新
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','COMPELETE');
BEGIN DBMS_MVIEW.REFRESH('MV_4','COMPELETE'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00904: "C2": invalid identifier
ORA-00904: "T"."C2": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
suk@ORA9I> EXEC DBMS_MVIEW.REFRESH('MV_4','FAST');
BEGIN DBMS_MVIEW.REFRESH('MV_4','FAST'); END;
*
ERROR at line 1:
ORA-12057: materialized view "SUK"."MV_4" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
--以上的測(cè)試結(jié)果證明了前面的推論實(shí)在正確的
?
?
五、總結(jié)
?
源表結(jié)構(gòu)變化
?
1、如果建立MV的DDL用到*(真正存儲(chǔ)在數(shù)據(jù)庫(kù))，則當(dāng)源表增加字段時(shí)，基于該源表的MV可以正常快速刷新，但不能完全刷新;當(dāng)源表刪除字段時(shí)，基于該源表的MV可以正常快速刷新，也可以正常完全刷新。
2、如果建立MV的DDL指定了具體字段，則當(dāng)源表增加字段時(shí)字段時(shí)，基于該源表的MV可以正常快速刷新，也可以正常完全刷新;當(dāng)源表刪除字段時(shí)，不能快速刷新，也不能完全刷新。
3、源表添加字段時(shí)，發(fā)生在新增字段的數(shù)據(jù)的變化對(duì)應(yīng)的記錄在快速刷新時(shí)會(huì)被刷新
基表結(jié)構(gòu)變化
4、無(wú)論是指定字段還是用*，如果基表添加字段，則不會(huì)影響快速刷新和完全刷新
5、無(wú)論是指定字段還是用*，如果基表刪除字段，則不能快速刷新和完全刷新
?
?