今天尝试构造物化视图日志来强制物化视图的刷新，结果没有成功。发现以前对物化视图的刷新理解的还不透彻。
利用触发器对物化视图刷新进行定制：http://yangtingkun.itpub.net/post/468/486225
看具体的例子：
SQL> CONN YANGTK/YANGTK@YTK92
已连接。
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));
表已创建。
SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB;
已创建25行。
SQL> COMMIT;
提交完成。
SQL> CREATE MATERIALIZED VIEW LOG ON T;
实体化视图日志已创建。
SQL> CREATE TABLE MV_T (ID NUMBER, NAME VARCHAR2(30), TIME DATE DEFAULT SYSDATE);
表已创建。
SQL> CREATE MATERIALIZED VIEW MV_T ON PREBUILT TABLE REFRESH FAST
2 AS SELECT * FROM T;
实体化视图已创建。
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T', 'C')
PL/SQL 过程已成功完成。
这是上一篇文章中的例子，打算添加触发器使得物化视图日志中的一条更新记录变为一条删除记录和一条插入记录。这样物化视图刷新的时候就会先执行删除，然后执行插入，对于物化视图而言，就会自动将SYSDATE加载到时间戳列。
SQL> CREATE OR REPLACE TRIGGER T
2 AFTER UPDATE ON T
3 FOR EACH ROW
4 BEGIN
5 DELETE MLOG$_T WHERE ID = :NEW.ID AND DMLTYPE$$ = 'U';
6 INSERT INTO MLOG$_T VALUES
7 (:NEW.ID, TO_DATE('4000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'D', 'O', '00');
8 INSERT INTO MLOG$_T VALUES
9 (:NEW.ID, TO_DATE('4000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'I', 'N', 'FF');
10 END;
11 /
触发器已创建
SQL> SELECT * FROM T WHERE ID = 26;
ID NAME
---------- ------------------------------
26 C
SQL> UPDATE T SET NAME = 'D' WHERE ID = 26;
已更新 1 行。
SQL> SELECT * FROM MLOG$_T;
ID SNAPTIME$$ D O CHANGE_VECTOR$$
---------- ------------------- - - --------------------------------------------------
26 4000-01-01 00:00:00 D O 00
26 4000-01-01 00:00:00 I N FF
SQL> SELECT * FROM MV_T WHERE ID = 26;
ID NAME TIME
---------- ------------------------------ -------------------
26 C 2009-06-21 00:14:42
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')
PL/SQL 过程已成功完成。
SQL> SELECT * FROM MV_T WHERE ID = 26;
ID NAME TIME
---------- ------------------------------ -------------------
26 D 2009-06-21 00:14:42
可以看到，触发器成功的修改了物化视图日志信息，在刷新之后，主表的UPDATE修改也更新到物化视图上，但是时间戳列并没有被更新。
莫非物化视图刷新的时候，并没有根据物化视图日志中的信息执行DELETE和INSERT，而是直接执行的UPDATE，只好通过TRACE来检查一下，Oracle具体执行了哪些操作：
SQL> UPDATE T SET NAME = 'E' WHERE ID = 26;
已更新 1 行。
SQL> SELECT * FROM MLOG$_T;
ID SNAPTIME$$ D O CHANGE_VECTOR$$
---------- ------------------- - - --------------------------------------------------
26 4000-01-01 00:00:00 D O 00
26 4000-01-01 00:00:00 I N FF
SQL> SELECT * FROM MV_T WHERE ID = 26;
ID NAME TIME
---------- ------------------------------ -------------------
26 D 2009-06-21 00:14:42
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
会话已更改。
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')
PL/SQL 过程已成功完成。
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
会话已更改。
SQL> SELECT * FROM MV_T WHERE ID = 26;
ID NAME TIME
---------- ------------------------------ -------------------
26 E 2009-06-21 00:14:42
检查对应的TRACE文件，看看Oracle实际上如何进行刷新的：
*** 2009-06-21 22:23:19.375
=====================
PARSING IN CURSOR #1 len=69 dep=0 uid=61 ct=42 lid=61 tim=46240728831 hv=4111402309 ad='6c6df49c'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'
END OF STMT
EXEC #1:c=0,e=50,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=46240728824
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 26472 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 5534967 p1=1413697536 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=61 ct=47 lid=61 tim=46246318661 hv=1010564567 ad='6bab130c'
BEGIN DBMS_MVIEW.REFRESH('MV_T'); END;
END OF STMT
PARSE #1:c=0,e=174,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=46246318656
BINDS #1:
XCTEND rlbk=0, rd_only=0
=====================
.
.
.
=====================
PARSING IN CURSOR #8 len=234 dep=1 uid=61 ct=3 lid=61 tim=46246372920 hv=2988923072 ad='6b8b7534'
SELECT DISTINCT LOG$."ID" FROM (SELECT MLOG$."ID" FROM "YANGTK"."MLOG$_T" MLOG$ WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$."ID") NOT IN (SELECT MAS_TAB$."ID" FROM "T" "MAS_TAB$" WHERE LOG$."ID" = MAS_TAB$."ID")
END OF STMT
PARSE #8:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=46246372914
BINDS #8:
bind 0: dty=12 mxl=07(07) mal=00 scl=00 pre=00 acflg=10 oacfl2=1 size=8 ffset=0
bfp=0344593c bln=07 avl=07 flg=05
value="6/21/2009 22:14:9"
EXEC #8:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=46246373059
FETCH #8:c=0,e=82,p=0,cr=8,cu=0,mis=0,r=0,dep=1,og=4,tim=46246373161
STAT #8 id=1 cnt=0 pid=0 pos=1 bj=0 p='SORT UNIQUE (cr=8 r=0 w=0 time=77 us)'
STAT #8 id=2 cnt=0 pid=1 pos=1 bj=0 p='FILTER (cr=8 r=0 w=0 time=57 us)'
STAT #8 id=3 cnt=1 pid=2 pos=1 bj=32459 p='TABLE ACCESS FULL MLOG$_T (cr=7 r=0 w=0 time=32 us)'
STAT #8 id=4 cnt=1 pid=2 pos=2 bj=32458 p='INDEX UNIQUE SCAN SYS_C002983 (cr=1 r=0 w=0 time=8 us)'
=====================
PARSING IN CURSOR #8 len=250 dep=1 uid=61 ct=3 lid=61 tim=46246373293 hv=4036264607 ad='6b8b1754'
SELECT CURRENT$."ID",CURRENT$."NAME" FROM (SELECT "T"."ID" "ID","T"."NAME" "NAME" FROM "T" "T") CURRENT$, (SELECT DISTINCT MLOG$."ID" FROM "YANGTK"."MLOG$_T" MLOG$ WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."ID" = LOG$."ID"
END OF STMT
PARSE #8:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=46246373289
BINDS #8:
bind 0: dty=12 mxl=07(07) mal=00 scl=00 pre=00 acflg=10 oacfl2=1 size=8 ffset=0
bfp=03436924 bln=07 avl=07 flg=05
value="6/21/2009 22:14:9"
EXEC #8:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=46246373434
=====================
PARSING IN CURSOR #9 len=64 dep=2 uid=61 ct=6 lid=61 tim=46246373594 hv=1114756011 ad='6b8a8894'
UPDATE "YANGTK"."MV_T" SET "ID" = :1,"NAME" = :2 WHERE "ID" = :1
END OF STMT
PARSE #9:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=46246373589
BINDS #9:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 acflg=13 oacfl2=1 size=24 ffset=0
bfp=0341ea44 bln=22 avl=02 flg=09
value=26
bind 1: dty=1 mxl=32(30) mal=00 scl=00 pre=00 acflg=13 oacfl2=1 size=32 ffset=0
bfp=0341ea5c bln=32 avl=01 flg=09
value="E"
bind 2: (No oacdef for this bind)
EXEC #9:c=0,e=164,p=0,cr=7,cu=1,mis=0,r=1,dep=2,og=4,tim=46246373913
FETCH #8:c=0,e=512,p=0,cr=16,cu=1,mis=0,r=1,dep=1,og=4,tim=46246373969
STAT #8 id=1 cnt=1 pid=0 pos=1 bj=0 p='NESTED LOOPS (cr=9 r=0 w=0 time=120 us)'
STAT #8 id=2 cnt=1 pid=1 pos=1 bj=0 p='VIEW (cr=7 r=0 w=0 time=79 us)'
STAT #8 id=3 cnt=1 pid=2 pos=1 bj=0 p='SORT UNIQUE (cr=7 r=0 w=0 time=63 us)'
STAT #8 id=4 cnt=1 pid=3 pos=1 bj=32459 p='TABLE ACCESS FULL MLOG$_T (cr=7 r=0 w=0 time=36 us)'
STAT #8 id=5 cnt=1 pid=1 pos=2 bj=32457 p='TABLE ACCESS BY INDEX ROWID T (cr=2 r=0 w=0 time=23 us)'
STAT #8 id=6 cnt=1 pid=5 pos=1 bj=32458 p='INDEX UNIQUE SCAN SYS_C002983 (cr=1 r=0 w=0 time=8 us)'
=====================
PARSING IN CURSOR #8 len=57 dep=1 uid=61 ct=2 lid=61 tim=46246374127 hv=3961430433 ad='6b8a6064'
INSERT INTO "YANGTK"."MV_T" ("ID","NAME") VALUES (:1,:2)
END OF STMT
PARSE #8:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=46246374124
STAT #9 id=1 cnt=0 pid=0 pos=1 bj=0 p='UPDATE (cr=7 r=0 w=0 time=72 us)'
STAT #9 id=2 cnt=1 pid=1 pos=1 bj=32464 p='TABLE ACCESS FULL MV_T (cr=7 r=0 w=0 time=31 us)'
STAT #3 id=1 cnt=1 pid=0 pos=1 bj=0 p='UPDATE (cr=1 r=0 w=0 time=62 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=7 us)'
=====================
.
.
.
=====================
PARSING IN CURSOR #1 len=55 dep=0 uid=61 ct=42 lid=61 tim=46252319576 hv=3614818515 ad='6c6dc664'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'
END OF STMT
PARSE #1:c=0,e=170,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=46252319571
BINDS #1:
EXEC #1:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=46252319673
由于篇幅太长，所以上面仅列出了和刷新过程关系最重要的步骤。根据Oracle执行的SQL可以看，Oracle在检查是否进行删除和插入时，并不是只根据MLOG$中的结果，还要关心主表中当前记录是否存在。
由于记录26在物化视图日志和主表中都存在，因此Oracle根本没有执行DELETE操作，而是改为执行了UPDATE操作。随后虽然有一个INSERT语句，但是Oracle根本没有执行。
想一想其实这种处理方法也是有道理的，如果一条记录多次被删除和插入。Oracle不用关心这个中间的过程，只要根据最终主表中的状态来确定物化视图是插入、更新还是删除就可以了。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-607078/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-607078/
扫一扫
1327
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
