物化檢視重新整理並非完全根據物化檢視日誌記錄
今天嘗試構造物化檢視日誌來強制物化檢視的重新整理，結果沒有成功。發現以前對物化檢視的重新整理理解的還不透徹。
利用觸發器對物化檢視重新整理進行定製：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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【物化檢視】根據物化檢視日誌快速重新整理物化檢視的過程
- Oracle如何根據物化檢視日誌快速重新整理物化檢視Oracle
- 物化檢視的快速重新整理測試與物化檢視日誌
- 【物化檢視】幾種物化檢視日誌分析
- 物化檢視日誌與增量重新整理
- 10G物化檢視PCT快速重新整理不再需要物化檢視日誌（三）
- 10G物化檢視PCT快速重新整理不再需要物化檢視日誌（二）
- 10G物化檢視PCT快速重新整理不再需要物化檢視日誌（一）
- 物化檢視日誌表被DROP後建立物化檢視報錯
- 物化檢視日誌的維護
- oracle物化檢視日誌系列(一)Oracle
- oracle物化檢視日誌系列(二)Oracle
- oracle物化檢視日誌系列(三)Oracle
- 物化檢視匯出匯入可能導致物化檢視日誌的失效
- 刷物化檢視並行並行
- 12c 物化檢視 - 理解完全重新整理的物化檢視工作原理
- 物化檢視妙用__表同步使用物化檢視方法
- oracle 物化檢視重新整理方法Oracle
- 物化檢視日誌對UPDATE的影響
- 物化檢視comlete重新整理會產生大量的日誌
- 多個物化檢視導致物化日誌無法及時更新
- oracle物化檢視Oracle
- 物化檢視學習筆記筆記
- 【筆記】 使用物化檢視（一）筆記
- 物化檢視詳解
- oracle 建立物化檢視Oracle
- Oracle 物化檢視建立Oracle
- materialized view (物化檢視)ZedView
- 物化檢視 on commitMIT
- 物化檢視日誌無法正常清除的解決方法
- 物化檢視日誌的重建 與 ORA-12034
- 普通檢視和物化檢視的區別
- oracle 物化檢視的自動重新整理方法Oracle
- 【ORACLE】物化檢視快速重新整理限制條件Oracle
- 物化檢視重新整理的問題及分析
- Oracle 物化檢視 快速重新整理 限制 說明Oracle
- MV (Materialed View) 物化檢視的重新整理組View
- calcite物化檢視詳解