物化檢視重新整理並非完全根據物化檢視日誌記錄

今天嘗試構造物化檢視日誌來強制物化檢視的重新整理，結果沒有成功。發現以前對物化檢視的重新整理理解的還不透徹。

利用觸發器對物化檢視重新整理進行定製：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不用關心這個中間的過程，只要根據最終主表中的狀態來確定物化檢視是插入、更新還是刪除就可以了。