Oracle 物化視圖日志

一、物化視圖日志是什么

oracle 的物化視圖的快速刷新要求必須建立物化視圖日志，通過物化視圖日志可以實現增量刷新功能。

官方文檔給出的對物化視圖日志的釋義：

A materialized view log is required on a master to perform a fast refresh on materialized views based on the master. When you create a materialized view log for a master table or master materialized view, Oracle creates an underlying table as the materialized view log. A materialized view log can hold the primary keys, rowids, or object identifiers of rows, or both, that have been updated in the master table or master materialized view. A materialized view log can also contain other columns to support fast refreshes of materialized views with subqueries.

The name of a materialized view log's table is MLOG$_master_name. The materialized view log is created in the same schema as the target master. One materialized view log can support multiple materialized views on its master table or master materialized view. As described in the previous section, the internal trigger adds change information to the materialized view log whenever a DML transaction has taken place on the target master.

       物化視圖日志在建立時有多種選項：可以指定為ROWID、PRIMARY KEY和OBJECTID幾種類型，同時還可以指定SEQUENCE或明確指定列名。不過上面這些情況產生的物化視圖日志的結構都不相同。這里要注意，當發生DML 操作時，內部的觸發器會把變化記錄到物化視圖日志里，也就是說物化視圖不支持DDL的同步，所以在物化視圖的編寫過程中不可使用select * from 的形式，因為這樣當基表發生變化時，物化視圖就會失效。

       物化視圖日志的名稱為MLOG$_后面跟基表的名稱，如果表名的長度超過20位，則只取前20位，當截短后出現名稱重復時，Oracle會自動在物化視圖日志名稱后面加上數字作為序號。

雖然物化視圖格式會有不同，但任何物化視圖都會包括如下列：

下面是一個primarykey的物化視圖日志：

zx@ORA11G>desc mlog$_employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID			  NUMBER(6)
 SNAPTIME$$			  DATE
 DMLTYPE$$			  VARCHAR2(1)
 OLD_NEW$$			  VARCHAR2(1)
 CHANGE_VECTOR$$		  RAW(255)
 XID$$				  NUMBER

相關解釋如下：

• SNAPTIME$$：用于表示刷新時間。

• DMLTYPE$$：用于表示DML操作類型，I表示INSERT，D表示DELETE，U表示UPDATE。

• OLD_NEW$$：用于表示這個值是新值還是舊值。N（EW）表示新值，O（LD）表示舊值，U表示UPDATE操作。

• CHANGE_VECTOR$$：表示修改矢量，用來表示被修改的是哪個或哪幾個字段。

INSERT和DELETE操作都是記錄集的，即INSERT和DELETE會影響整條記錄。而UPDATE操作是字段集的，UPDATE操作可能會更新整條記錄的所有字段，也可能只更新個別字段。

無論從性能上考慮還是從數據的一致性上考慮，物化視圖刷新時都應該是基于字段集。Oracle就是通過CHANGE_VECTOR$$列來記錄每條記錄發生變化的字段包括哪些。

基于主鍵、ROWID和OBJECT ID的物化視圖日志在CHANGE_VECTOR$$上略有不同，但是總體設計的思路是一致的。

CHANGE_VECTOR$$列是RAW類型，其實Oracle采用的方式就是用每個BIT位去映射一個列。

比如：第一列被更新設置為02，即00000010。第二列設置為04，即00000100，第三列設置為08，即00001000。當第一列和第二列同時被更新，則設置為06，00000110。如果三列都被更新，設置為0E，00001110。

依此類推，第4列被更新時為10，第5列20，第6列40，第7列80，第8列0001。當第1000列被更新時，CHANGE_VECTOR$$的長度為1000/4+2為252。

除了可以表示UPDATE的字段，還可以表示INSERT和DELETE。DELETE操作CHANGE_VECTOR列為全0，具體個數由基表的列數決定。INSERT操作的最低位為FE如果基表列數較多，而存在高位的話，所有的高位都為FF。如果INSERT操作是前面討論過的由UPDATE操作更新了主鍵造成的，則這個INSERT操作對應的CHANGEVECTOR列為全FF。

如果WITH后面跟了ROWID，則物化視圖日志中會包含：M_ROW$$：用來存儲發生變化的記錄的ROWID。

如果WITH后面跟了PRIMARY KEY，則物化視圖日志中會包含主鍵列。

如果WITH后面跟了OBJECT ID，則物化視圖日志中會包含：SYS_NC_OID$：用來記錄每個變化對象的對象ID。

如果WITH后面跟了SEQUENCE，則物化視圖日子中會包含：SEQUENCE$$：給每個操作一個SEQUENCE號，從而保證刷新時按照順序進行刷新。

如果WITH后面跟了一個或多個COLUMN名稱，則物化視圖日志中會包含這些列。

二、根據物化視圖日志來快速刷新數據過程

2.1 創建測試環境

zx@ORA11G>create table t (id number,name varchar2(10),address varchar2(10));

Table created.

zx@ORA11G>create materialized view log on t with rowid,sequence (id,name) including new values;

Materialized view log created.

zx@ORA11G>desc mlog$_t
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 ID				  NUMBER
 NAME				  VARCHAR2(10)
 M_ROW$$			  VARCHAR2(255)
 SEQUENCE$$			  NUMBER
 SNAPTIME$$			  DATE
 DMLTYPE$$			  VARCHAR2(1)
 OLD_NEW$$			  VARCHAR2(1)
 CHANGE_VECTOR$$		  RAW(255)
 XID$$				  NUMBER

• ID和NAME是建立物化視圖日志時指定的基表中的列，它們記錄每次DML操作對應的ID和NAME的值。

• M_ROW$$：保存基表的ROWID信息，根據M_ROW$$中的信息可以定位到發生DML操作的記錄。

• SEQUENCE$$：根據DML操作發生的順序記錄序列的編號，當刷新時，根據SEQUENCE中的順序就可以和基表中的執行順序保持一致。

• SNAPTIME$$：列記錄了刷新操作的時間。

• DMLTYPE$$：的記錄值I、U和D，表示操作是INSERT、UPDATE還是DELETE。

• OLD_NEW$$：表示物化視圖日志中保存的信息是DML操作之前的值（舊值）還是DML操作之后的值（新值）。除了O和N這兩種類型外，對于UPDATE操作，還可能表示為U。

• CHANGE_VECTOR$$：記錄DML操作發生在那個或那幾個字段上

      當刷新物化視圖時，只需要根據SEQUENCE列給出的順序，通過M_ROW$$定位到基表的記錄，如果是UPDATE操作，通過CHANGE_VECTOR$$定位到字段，然后根據基表中的數據重復執行DML操作。

       如果物化視圖日志只針對一個物化視圖，那么刷新過程就是這么簡單，還需要做的不過是在刷新之后將物化視圖日志清除掉。但是，Oracle的物化視圖日志是可以同時支持多個物化視圖的快速刷新的，也就是說，物化視圖在刷新時還必須判斷哪些物化視圖日志記錄是當前物化視圖刷新需要的，哪些是不需要的。而且，物化視圖還必須確定，在刷新物化視圖后，物化視圖日志中哪些記錄是需要清除的，哪些是不需要清除的。

        回顧一下物化視圖日志的結構，發現只剩下一個SHAPTIME$$列，那么Oracle如何僅通過這一列就完成了對多個物化視圖的支持呢？

2.2 下面建立一個小例子，通過例子來進行說明：

使用上文中建立的表和物化視圖日志，下面對這個表建立三個快速刷新的物化視圖，并對t表執行DML操作：

zx@ORA11G>create materialized view mv_t_id 
  2  refresh fast
  3  as select id,count(*) 
  4  from t
  5  group by id;

Materialized view created.

zx@ORA11G>create materialized view mv_t_name 
  2  refresh fast
  3  as select name,count(*) 
  4  from t
  5  group by name;

Materialized view created.

zx@ORA11G>create materialized view mv_t_both 
  2  refresh fast
  3  as select id,name,count(*) 
  4  from t
  5  group by id,name;

Materialized view created.

zx@ORA11G>insert into t values (1, 'zx', 'hb');

1 row created.

zx@ORA11G>insert into t values (2, 'wl', 'sd');

1 row created.

insert into t values (3, 'yc', 'bj');

1 row created.

zx@ORA11G>update t set address = 'bj_cp' where id = 3;

1 row updated.

zx@ORA11G>delete from t where id = 2;

1 row deleted.

zx@ORA11G>commit;

Commit complete.

查詢物化視圖日志，可以查看每次dml操作都有對應的日志

zx@ORA11G>col M_ROW$$ for a30
zx@ORA11G>col change_vector$$ for a30
zx@ORA11G>set num 20
zx@ORA11G>select * from mlog$_t;

                  ID NAME       M_ROW$$                                  SEQUENCE$$ SNAPTIME$$        D O CHANGE_VECTOR$$                               XID$$
-------------------- ---------- ------------------------------ -------------------- ----------------- - - ------------------------------ --------------------
                   1 zx         AAAVs6AAEAAAAJVAAA                                8 40000101 00:00:00 I N FE                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                                9 40000101 00:00:00 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               10 40000101 00:00:00 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               11 40000101 00:00:00 U U 08                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               12 40000101 00:00:00 U N 08                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                               13 40000101 00:00:00 D O 00                                 2814882911093459

6 rows selected.

當發生了DML操作后，物化視圖日志中的SNAPTIME$$列保持的值是40000101 00:00:00。這個值表示這條記錄還沒有被任何物化視圖刷新過。第一個刷新這些記錄的物化視圖會將SNAPTIME$$的值更新為物化視圖當前的刷新時間。

刷新一個物化視圖，并再次查看物化視圖日志：

zx@ORA11G>exec dbms_mview.refresh('MV_T_ID');

PL/SQL procedure successfully completed.

zx@ORA11G>select * from mlog$_t;

                  ID NAME       M_ROW$$                                  SEQUENCE$$ SNAPTIME$$        D O CHANGE_VECTOR$$                               XID$$
-------------------- ---------- ------------------------------ -------------------- ----------------- - - ------------------------------ --------------------
                   1 zx         AAAVs6AAEAAAAJVAAA                                8 20170809 15:58:30 I N FE                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                                9 20170809 15:58:30 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               10 20170809 15:58:30 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               11 20170809 15:58:30 U U 08                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               12 20170809 15:58:30 U N 08                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                               13 20170809 15:58:30 D O 00                                 2814882911093459

6 rows selected.

Oracle根據數據字典中的信息可以知道表T上建立了三個物化視圖，因此，MV_T_ID刷新完之后，不會刪除物化視圖記錄。但SNAPTIME$$列對應的時候修改為MV_T_ID物化視圖刷新時的時間

Oracle的數據字典中還保存著每個物化視圖上次刷新的時間和當前的刷新狀態。

zx@ORA11G>select name,master,last_refresh from user_mview_refresh_times;

NAME                           MASTER                         LAST_REFRESH
------------------------------ ------------------------------ -----------------
MV_T_BOTH                      T                              20170809 15:45:10
MV_T_ID                        T                              20170809 15:58:30
MV_T_NAME                      T                              20170809 15:45:05

zx@ORA11G>select mview_name,last_refresh_date, staleness from user_mviews;

MVIEW_NAME                     LAST_REFRESH_DATE STALENESS
------------------------------ ----------------- -------------------
MV_T_BOTH                      20170809 15:45:10 NEEDS_COMPILE
MV_T_ID                        20170809 15:58:30 FRESH
MV_T_NAME                      20170809 15:45:05 NEEDS_COMPILE

       這些視圖中記錄了每個物化視圖上次執行刷新操作的時間，并且給出每個物化視圖中的數據是否是和基表同步的。由于MV_T_ID剛剛進行了刷新，因此狀態是FRESH，而另外兩個由于在刷新（建立）之后，基表又進行了DML操作，因此狀態為NEEDS_COMPILE。如果這時對基表進行DML操作，則MV_T_ID的狀態也會變為NEEDS_COMPILE。

zx@ORA11G>insert into t values (4, 'zf', 'sd');

1 row created.

zx@ORA11G>commit;

Commit complete.

zx@ORA11G>select mview_name,last_refresh_date, staleness from user_mviews;

MVIEW_NAME                     LAST_REFRESH_DATE STALENESS
------------------------------ ----------------- -------------------
MV_T_BOTH                      20170809 15:45:10 NEEDS_COMPILE
MV_T_ID                        20170809 15:58:30 NEEDS_COMPILE
MV_T_NAME                      20170809 15:45:05 NEEDS_COMPILE

zx@ORA11G>select * from mlog$_t;

                  ID NAME       M_ROW$$                                  SEQUENCE$$ SNAPTIME$$        D O CHANGE_VECTOR$$                               XID$$
-------------------- ---------- ------------------------------ -------------------- ----------------- - - ------------------------------ --------------------
                   1 zx         AAAVs6AAEAAAAJVAAA                                8 20170809 15:58:30 I N FE                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                                9 20170809 15:58:30 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               10 20170809 15:58:30 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               11 20170809 15:58:30 U U 08                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               12 20170809 15:58:30 U N 08                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                               13 20170809 15:58:30 D O 00                                 2814882911093459
                   4 zf         AAAVs6AAEAAAAJVAAD                               14 40000101 00:00:00 I N FE                                  844463584838593

       下面刷新物化視圖MV_T_NAME，刷新操作的判斷依據是，只刷新SNAPTIME$$列大于當前物化視圖的LAST_REFRESH_DATE的記錄，由于物化視圖日志中所有記錄的SNAPTIME$$的值都比物化視圖MV_T_ID_NAME上次刷新的時間點大，因此會刷新所有記錄。對于SNAPTIME$$列的值是40000101 00:00:00的記錄，物化視圖會把SNAPTIME$$列的值更新為當前刷新時間，對于那些已經被更新過的SNAPTIME$$列，則保持原值。

zx@ORA11G>exec dbms_mview.refresh('MV_T_NAME');

PL/SQL procedure successfully completed.

zx@ORA11G>select mview_name,last_refresh_date, staleness from user_mviews;

MVIEW_NAME                     LAST_REFRESH_DATE STALENESS
------------------------------ ----------------- -------------------
MV_T_BOTH                      20170809 15:45:10 NEEDS_COMPILE
MV_T_ID                        20170809 15:58:30 NEEDS_COMPILE
MV_T_NAME                      20170809 16:16:01 FRESH

zx@ORA11G>select * from mlog$_t;

                  ID NAME       M_ROW$$                                  SEQUENCE$$ SNAPTIME$$        D O CHANGE_VECTOR$$                               XID$$
-------------------- ---------- ------------------------------ -------------------- ----------------- - - ------------------------------ --------------------
                   1 zx         AAAVs6AAEAAAAJVAAA                                8 20170809 15:58:30 I N FE                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                                9 20170809 15:58:30 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               10 20170809 15:58:30 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               11 20170809 15:58:30 U U 08                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               12 20170809 15:58:30 U N 08                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                               13 20170809 15:58:30 D O 00                                 2814882911093459
                   4 zf         AAAVs6AAEAAAAJVAAD                               14 20170809 16:16:01 I N FE                                  844463584838593

7 rows selected.

如果這時再次刷新物化視圖MV_T_ID，則只有ID=4的這條記錄的SNAPTIME$$的時間點大于MV_T_ID上次刷新的時間點，因此，只刷新這一條記錄，且不會改變SNAPTIME$$的值。

zx@ORA11G>exec dbms_mview.refresh('MV_T_ID');

PL/SQL procedure successfully completed.

zx@ORA11G>select mview_name,last_refresh_date, staleness from user_mviews;

MVIEW_NAME                     LAST_REFRESH_DATE STALENESS
------------------------------ ----------------- -------------------
MV_T_BOTH                      20170809 15:45:10 NEEDS_COMPILE
MV_T_ID                        20170809 16:17:43 FRESH
MV_T_NAME                      20170809 16:16:01 FRESH

zx@ORA11G>select * from mlog$_t;

                  ID NAME       M_ROW$$                                  SEQUENCE$$ SNAPTIME$$        D O CHANGE_VECTOR$$                               XID$$
-------------------- ---------- ------------------------------ -------------------- ----------------- - - ------------------------------ --------------------
                   1 zx         AAAVs6AAEAAAAJVAAA                                8 20170809 15:58:30 I N FE                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                                9 20170809 15:58:30 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               10 20170809 15:58:30 I N FE                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               11 20170809 15:58:30 U U 08                                 2814882911093459
                   3 yc         AAAVs6AAEAAAAJVAAC                               12 20170809 15:58:30 U N 08                                 2814882911093459
                   2 wl         AAAVs6AAEAAAAJVAAB                               13 20170809 15:58:30 D O 00                                 2814882911093459
                   4 zf         AAAVs6AAEAAAAJVAAD                               14 20170809 16:16:01 I N FE                                  844463584838593

到目前為止，還沒有看到過物化視圖日志的清除，其實每次進行完刷新，物化視圖日志都會試圖刪除沒有用的物化視圖日志記錄。物化視圖日志記錄的刪除條件是刪除那些SNAPTIME$$列小于等于基表所有物化視圖的上次刷新時間。在上面的例子中，由于MV_T_BOTH一直沒有刷新，因此它的LAST_REFRESH_DATE比物化視圖日志中所有記錄的值都小，因此，一直沒有發生物化視圖日志記錄清除的現象。

zx@ORA11G>insert into t values (5, 'zq', 'jx');

1 row created.

zx@ORA11G>commit;

Commit complete.

zx@ORA11G>exec dbms_mview.refresh('MV_T_BOTH');

PL/SQL procedure successfully completed.

zx@ORA11G>select mview_name,last_refresh_date, staleness from user_mviews;

MVIEW_NAME                     LAST_REFRESH_DATE STALENESS
------------------------------ ----------------- -------------------
MV_T_BOTH                      20170809 16:19:51 FRESH
MV_T_ID                        20170809 16:17:43 NEEDS_COMPILE
MV_T_NAME                      20170809 16:16:01 NEEDS_COMPILE

zx@ORA11G>select * from mlog$_t;

                  ID NAME       M_ROW$$                                  SEQUENCE$$ SNAPTIME$$        D O CHANGE_VECTOR$$                               XID$$
-------------------- ---------- ------------------------------ -------------------- ----------------- - - ------------------------------ --------------------
                   5 zq         AAAVs6AAEAAAAJVAAE                               15 20170809 16:19:51 I N FE                                 2251898597934032

物化視圖MV_T_BOTH刷新了物化視圖中的每條記錄，更新了ID=5的記錄的SNAPTIME$$時間，并清除了其它所有物化視圖日志記錄。

總結：

      物化視圖在刷新時，會刷新所有SNAPTIME$$大于本物化視圖上次刷新時間的記錄，并將所有是40000101 00:00:00的記錄更新為當前刷新時間。對于其他大于上次刷新時間的記錄，只刷新不更改。這樣，當刷新執行完以后，數據字典中記錄當前物化視圖的上次刷新時間為當前時刻，這保證了物化視圖日志中目前所有的記錄都小于或等于刷新時間。因此，每個物化視圖只要刷新大于上次刷新時間的記錄，且保證每次刷新后，所有記錄的時間都小于等于上次刷新時間，那么無論有多少個物化視圖，就可以互不影響的使用同一個物化視圖日志進行快速刷新了。當物化視圖刷新完之后，會清除那些SNAPTIME$$列小于所有物化視圖的上次刷新時間的記錄，而這些記錄已經被所有的物化視圖都刷新過了，保存在物化視圖日志中已經沒有意義了。

參考：http://blog.csdn.net/tianlesoftware/article/details/7720580

http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#i30732

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6003.htm#SQLRF01303