物化视图的创建写法与刷新问题【转】

首先看看下面两个物化视图有什么区别：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE MATERIALIZED VIEW LOG ON T;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW MV_T1 REFRESH FAST AS SELECT * FROM T;

实体化视图已创建。

SQL> CREATE MATERIALIZED VIEW MV_T2 REFRESH FAST AS SELECT T.* FROM T;

实体化视图已创建。

唯一的区别在于MV_T2在SELECT *的时候指明了T.*，不要小看这点区别，就是T.*和*的区别，就会导致两个物化视图在刷新时的表现截然不同。

为了展示问题，需要对基表添加一列，这对于普通的物化视图来说不是问题。物化视图日志会自动支持新增的列。

SQL> INSERT INTO T VALUES (1, 'YTK');

已创建 1 行。

SQL> ALTER TABLE T ADD AGE NUMBER(3);

表已更改。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T1')

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T2')

PL/SQL 过程已成功完成。

快速刷新两个物化视图都没有产生问题，那么看看完全刷新的情况：

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T1', 'C')

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T2', 'C')
BEGIN DBMS_MVIEW.REFRESH('MV_T2', 'C'); END;

*
ERROR 位于第 1 行:
ORA-12018: 在创建"YANGTK"."MV_T2" 的代码时出现以下错误
ORA-00904: "AGE": 无效的标识符
ORA-06512: 在"SYS.DBMS_SNAPSHOT", line 794
ORA-06512: 在"SYS.DBMS_SNAPSHOT", line 851
ORA-06512: 在"SYS.DBMS_SNAPSHOT", line 832
ORA-06512: 在line 1

差异终于体现了。MV_T1的完全刷新没有任何问题，而对于MV_T2，完全刷新导致了错误的产生。

这个测试说明了两个问题。首先，两个物化视图是有区别的，否则不会一个在完全刷新时报错，而另一个在完全刷新时不报错。其次，物化视图的快速刷新和完全刷新的方法并不一样，否则不会快速刷新正常而完全刷新报错。

先看第一个问题：

SQL> COL QUERY FORMAT A80
SQL> SELECT MVIEW_NAME, QUERY FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_T_';

MVIEW_NAME QUERY
------------------------------ ------------------------------------------------
MV_T1 SELECT "T"."ID" "ID","T"."NAME" "NAME" FROM "T" "T"
MV_T2 SELECT T.* FROM T

由于MV_T2在*前面添加了T，物化视图的定义并不像MV_T1那样被全部解析为列的列表方式。在完全刷新的时候，MV_T2根据T重新展开，而此时由于增加了新的列，导致物化视图同步时查询列和插入列不匹配。

而MV_T1则没有这个问题，MV_T1已经在创建物化视图的时候将列定义展开，因此新增的字段对MV_T1没有任何影响。

最后看看快速刷新和完全刷新的区别，在*前面添加了T之后，物化视图的定义并没有被展开。而物化视图快速刷新要顺利完成，就要求数据字典中必须有专门的地方来保留快速刷新需要使用的SQL语句：

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T1')

PL/SQL 过程已成功完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

TRACE文件内容比较长，下面仅摘录关键部分：

PARSING IN CURSOR #10 len=249 dep=1 uid=0 ct=3 lid=0 tim=18446744072015785716 hv=2055365415 ad='7db80b40'
SELECT operation#, cols, sql_txt, tabnum, fcmaskvec, ejmaskvec, setnum FROM sys.snap_refop$ WHERE ((operation# >= 0 AND operation# <= 6) OR operation# = 10) AND sowner = :1 AND vname = :2 AND instsite = :3 ORDER BY tabnum, setnum, operation#
END OF STMT
PARSE #10:c=0,e=646,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=18446744072015785710
EXEC #10:c=0,e=340,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18446744072015786217
FETCH #10:c=0,e=128,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=18446744072015786412
FETCH #10:c=0,e=14,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=18446744072015786476
FETCH #10:c=0,e=12,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=18446744072015786528
FETCH #10:c=0,e=11,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=18446744072015786582
FETCH #10:c=0,e=11,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=18446744072015786634
STAT #10 id=1 cnt=5 pid=0 pos=1 bj=0 p='SORT ORDER BY (cr=3 r=0 w=0 time=118 us)'
STAT #10 id=2 cnt=5 pid=1 pos=1 bj=188 p='TABLE ACCESS BY INDEX ROWID SNAP_REFOP$ (cr=3 r=0 w=0 time=77 us)'
STAT #10 id=3 cnt=6 pid=2 pos=1 bj=189 p='INDEX RANGE SCAN I_SNAP_REFOP1 (cr=1 r=0 w=0 time=28 us)'
STAT #3 id=1 cnt=1 pid=0 pos=1 bj=0 p='UPDATE (cr=1 r=0 w=0 time=121 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 bj=181 p='INDEX UNIQUE SCAN I_SNAP1 (cr=1 r=0 w=0 time=10 us)'

下面访问SYS.SNAP_REFOP$，看看其中的内容：

SQL> SELECT VNAME, SQL_TXT FROM SYS.SNAP_REFOP$ WHERE VNAME LIKE 'MV_T_';

VNAME SQL_TXT
-------- -------------------------------------------------------------
MV_T1 SELECT DISTINCT LOG$."ID" FROM (SELECT MLOG$."ID" FROM "YANGTK"."MLOG$_T" MLOG$
MV_T1 SELECT CURRENT$."ID",CURRENT$."NAME" FROM (SELECT "T"."ID" "ID","T"."NAME" "NAME
MV_T1 DELETE FROM "YANGTK"."MV_T1" SNAP$ WHERE "ID" = :1
MV_T1 INSERT INTO "YANGTK"."MV_T1" ("ID","NAME") VALUES (:1,:2)
MV_T1 UPDATE "YANGTK"."MV_T1" SET "ID" = :1,"NAME" = :2 WHERE "ID" = :1
MV_T2 SELECT DISTINCT LOG$."ID" FROM (SELECT MLOG$."ID" FROM "YANGTK"."MLOG$_T" MLOG$
MV_T2 SELECT CURRENT$."ID",CURRENT$."NAME" FROM (SELECT "T"."ID" "ID","T"."NAME" "NAME
MV_T2 DELETE FROM "YANGTK"."MV_T2" SNAP$ WHERE "ID" = :1
MV_T2 INSERT INTO "YANGTK"."MV_T2" ("ID","NAME") VALUES (:1,:2)
MV_T2 UPDATE "YANGTK"."MV_T2" SET "ID" = :1,"NAME" = :2 WHERE "ID" = :1
MV_T1 INSERT INTO "YANGTK"."MV_T1"("ID","NAME") SELECT "T"."ID","T"."NAME" FROM "T" "T

已选择

11行。

Oracle将快速刷新的语句保存在单独的表中，因此添加字段后，两种方法都不会影响物化视图的快速刷新。