物化視圖幾個知識點
?
源表：物化視圖數據源對應的表
基表：物化視圖對應的表
本文主要內容包括：
1、如何使源表的數據變化不影響物化視圖的快速刷新
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.
--準備4種方法測試的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.
?
一、如何修改源表數據，而不會產生MLOG$_XXX
物化視圖在快速刷新時是根據MLOG$_XXX的記錄來決定那些數據需要刷新的，所以，如果想要源表修改的數據不被刷新的話，就需要把MLOG$_XXX對應的記錄去掉。
?
1、用函數包
?
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');
--用這個過程可以使得對源表的DML操作不產生MLOG，影響范圍是從BEGIN_TABLE_REORGANIZATION到END_TABLE_REORGANIZATION其間
PL/SQL procedure successfully completed.
suk@ORA9I> COMMIT;--一定要執行commit，否則還會產生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.
--注意：對于表的DML非常頻繁，如果只是想讓某小部分數據不產生日志，則這種方法不適合。可以用第二種方法。
2、刪除MLOG$記錄
第二種方法很直接，就是直接刪除不想被刷新的數據對應的修改日志。
這種方法的難點是如何準確找出那些是你需要刪除的日志。方法很麻煩，這里不詳細說了。
?
二、如何修改物化視圖數據時，不產生USLOG_XXX
這種情況只能用手工刪除USLOG$_XXX的方法了。
?
三、源表結構發生變化時
1、源表添加字段
--添加字段
suk@ORA9I> ALTER TABLE T_MV ADD(COL3 NUMBER);
Table altered.
--對mv_1進行兩種方法刷新
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.
--對mv_2進行兩種方法刷新
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.
--對mv_3進行兩種方法刷新
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.
--對mv_4進行兩種方法刷新
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.
--從以上測試結果可以看出，源表添加字段時，只有MV_4在完全刷新時會出錯。為什么會這樣呢？
--先看看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
--看每一個MV的DDL的最后一行，不難發現問題了。
--在前三種情況下，oracle在創建MV時會翻譯成當前源表對應的字段名;但第四種情況則不然，它是在刷新時才翻譯成源表對應的字段，如果源表的結構發生變化，那很明顯，MV刷新會出現問題。
--那MV_4為什么快速刷新就不會出錯呢？通過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)
--可以看出，完全刷新時，是根據源表的結構進行刷新的
--快速刷新時，是根據MV的結構進行刷新的
2、新添加的字段數據發生變化，快速刷新是否會刷新該記錄
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文件中可以發現如下語句：
UPDATE "SUK"."MV_1" SET "C1" = :1,"C2" = :2
WHERE
"C1" = :1
--說明在源表中且在MV不存在的字段的數值發生變化，MV也會刷新這條數據。且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都包同樣的錯誤.....
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.
--其實這個結果可以根據上一步推斷出來了，現在用試驗也證明了。
--根本原因就是建立MV的DDL不同，也就是是否指定*導致的
四、基表結構發生變化時
剛才討論了源表的結構變化對MV刷新的影響，下面討論基表的結構對MV刷新的影響。
?
這個問題相對簡單一點，我們知道，修改基表不會對MV建立的DDL造成影響，也就是不會改變MV的刷新語句，所以，很容易得到以下結論：
1、如果基表添加字段，則不會影響快速刷新和完全刷新
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.
----對mv_1進行兩種方法刷新
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.
--對mv_4進行兩種方法刷新
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.
--對mv_1進行兩種方法刷新
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
--對mv_4進行兩種方法刷新
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
--以上的測試結果證明了前面的推論實在正確的
?
?
五、總結
?
源表結構變化
?
1、如果建立MV的DDL用到*(真正存儲在數據庫)，則當源表增加字段時，基于該源表的MV可以正常快速刷新，但不能完全刷新;當源表刪除字段時，基于該源表的MV可以正常快速刷新，也可以正常完全刷新。
2、如果建立MV的DDL指定了具體字段，則當源表增加字段時字段時，基于該源表的MV可以正常快速刷新，也可以正常完全刷新;當源表刪除字段時，不能快速刷新，也不能完全刷新。
3、源表添加字段時，發生在新增字段的數據的變化對應的記錄在快速刷新時會被刷新
基表結構變化
4、無論是指定字段還是用*，如果基表添加字段，則不會影響快速刷新和完全刷新
5、無論是指定字段還是用*，如果基表刪除字段，則不能快速刷新和完全刷新
?
?