物化檢視日誌對UPDATE的影響

物化檢視日誌的功能是記錄基表的DML修改，使得物化檢視可以透過物化檢視日誌上的記錄進行快速重新整理。

那麼物化檢視日誌記錄基本修改的同時，是否會對基表的DML產生一定的影響。

 

 

由於物化檢視日誌會將UPDATE主鍵的操作變成DELETE和INSERT，那麼實際上基表執行的是什麼操作呢，下面做一個簡單的測試驗證一下，首先是不包含物化檢視日誌的情況：

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

表已建立。

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

已建立 1 行。

SQL> INSERT INTO T VALUES (2, 'A');

已建立 1 行。

SQL> UPDATE T SET ID = 3 WHERE ID = 1;

已更新 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         31 INACTIVE
         2         32 CURRENT
         3         30 INACTIVE

SQL> COL MEMBER FORMAT A50
SQL> SELECT GROUP#, MEMBER FROM V$LOGFILE;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 E:\ORACLE\ORADATA\YTK92\REDO01.LOG
         2 E:\ORACLE\ORADATA\YTK92\REDO02.LOG
         3 E:\ORACLE\ORADATA\YTK92\REDO03.LOG

SQL> ALTER SYSTEM SWITCH LOGFILE;

系統已更改。

SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('E:\ORACLE\ORADATA\YTK92\REDO02.LOG', SYS.DBMS_LOGMNR.NEW)

PL/SQL 過程已成功完成。

SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL 過程已成功完成。

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_NAME = 'T' AND SEG_OWNER = USER;

SQL_REDO
-------------------------------------------------------------------------------------
CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));
insert into "YANGTK"."T"("ID","NAME") values ('1','A');
insert into "YANGTK"."T"("ID","NAME") values ('2','A');
update "YANGTK"."T" set "ID" = '3' where "ID" = '1' and ROWID = 'AAAH1NAAJAAAAJoAAA';

SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR

PL/SQL 過程已成功完成。

下面新增物化檢視日誌，再執行一下更新操作：

SQL> CREATE MATERIALIZED VIEW LOG ON T;

實體化檢視日誌已建立。

SQL> UPDATE T SET NAME = 'B' WHERE ID = 2;

已更新 1 行。

SQL> UPDATE T SET ID = 1 WHERE ID = 3;

已更新 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT ID, DMLTYPE$$, OLD_NEW$$ FROM MLOG$_T;

        ID D O
---------- - -
         2 U U
         3 D O
         1 I N

可以看到，對於非主鍵的更新再物化檢視中就是一條更新記錄，而對於主鍵的更新，再物化檢視日誌中記錄了兩條，一條刪除，一條插入。

那麼實際上Oracle是如何執行的，看看LOGMNR得到的結果：

SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         31 INACTIVE
         2         32 ACTIVE
         3         33 CURRENT

SQL> ALTER SYSTEM SWITCH LOGFILE;

系統已更改。

SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('E:\ORACLE\ORADATA\YTK92\REDO03.LOG', SYS.DBMS_LOGMNR.NEW)

PL/SQL 過程已成功完成。

SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL 過程已成功完成。

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_NAME = 'T' AND SEG_OWNER = USER;

SQL_REDO
---------------------------------------------------------------------------------------
update "YANGTK"."T" set "NAME" = 'B' where "NAME" = 'A' and ROWID = 'AAAH1NAAJAAAAJoAAB';
update "YANGTK"."T" set "ID" = '1' where "ID" = '3' and ROWID = 'AAAH1NAAJAAAAJoAAA';

SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR

PL/SQL 過程已成功完成。

可以看到，Oracle實際上執行的仍然是UPDATE語句，而不是DELETE加INSERT語句，物化檢視日誌上只不過是為了方便記錄的假象。

SQL> UPDATE T SET ID = 2 WHERE ID = 2;

已更新 1 行。

SQL> DELETE T WHERE ID = 1;

已刪除 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         34 CURRENT
         2         32 INACTIVE
         3         33 ACTIVE

SQL> ALTER SYSTEM SWITCH LOGFILE;

系統已更改。

SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('E:\ORACLE\ORADATA\YTK92\REDO01.LOG', SYS.DBMS_LOGMNR.NEW)

PL/SQL 過程已成功完成。

SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL 過程已成功完成。

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_NAME = 'T' AND SEG_OWNER = USER;

SQL_REDO
-------------------------------------------------------------------------------------------
update "YANGTK"."T" set "ID" = '2' where "ID" = '2' and ROWID = 'AAAH1NAAJAAAAJoAAB';
delete from "YANGTK"."T" where "ID" = '1' and "NAME" = 'A' and ROWID = 'AAAH1NAAJAAAAJoAAA';

SQL> SELECT ID, DMLTYPE$$, OLD_NEW$$ FROM MLOG$_T;

        ID D O
---------- - -
         2 U U
         3 D O
         1 I N
         2 U U
         1 D O

SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR

PL/SQL 過程已成功完成。

再看一下UPDATE語句主鍵的值不發生變化的情況，這時物化檢視日誌並沒有拆分成DELETE和INSERT，顯然，把UPDATE變成DELETE和INSERT語句只是物化檢視日誌的行為，而不是物化檢視基表上實際執行的情況。