物化视图几个知识点
源表：物化视图数据源对应的表
基表：物化视图对应的表
本文主要内容包括：
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、无论是指定字段还是用*，如果基表删除字段，则不能快速刷新和完全刷新