ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 物化视图的创建写法与刷新问题

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

原创 Linux操作系统 作者:yangtingkun 时间:2007-08-03 00:00:00 0 删除 编辑

前些天和space6212讨论了一下这个问题,这里简单记录一下。


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

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 oct=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 obj=0 op='SORT ORDER BY (cr=3 r=0 w=0 time=118 us)'
STAT #10 id=2 cnt=5 pid=1 pos=1 obj=188 op='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 obj=189 op='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 obj=0 op='UPDATE (cr=1 r=0 w=0 time=121 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=181 op='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将快速刷新的语句保存在单独的表中,因此添加字段后,两种方法都不会影响物化视图的快速刷新。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69373/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10352749