oracle物化視圖日誌結構

oracle物化視圖日誌結構

      物化視圖的快速重新整理要求基本必須建立物化視圖日誌，這篇文章簡單描述一下物化視圖日誌中各個欄位的含義和用途。

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

      物化視圖日誌在建立時有多種選項：可以指定為ROWID、PRIMARY KEY和OBJECT ID幾種類型，同時還可以指定SEQUENCE或明確指定列名。上面這些情況產生的物化視圖日誌的結構都不相同。

                 

任何物化視圖都會包括的4列：
SNAPTIME$$：用於表示重新整理時間。
DMLTYPE$$：用於表示DML操作類型，I表示INSERT，D表示DELETE，U表示UPDATE。
OLD_NEW$$：用於表示這個值是新值還是舊值。N（EW）表示新值，O（LD）表示舊值，U表示UPDATE操作。
CHANGE_VECTOR$$：表示修改向量，用來表示被修改的是哪個或哪幾個欄位。
如果WITH後面跟了ROWID，則物化視圖日誌中會包含：M_ROW$$：用來儲存發生變化的記錄的ROWID。
如果WITH後面跟了PRIMARY KEY，則物化視圖日誌中會包含主鍵列。
如果WITH後面跟了OBJECT ID，則物化視圖日誌中會包含：SYS_NC_OID$：用來記錄每個變化對象的對象ID。
如果WITH後面跟了SEQUENCE，則物化視圖日子中會包含：SEQUENCE$$：給每個操作一個SEQUENCE號，從而保證重新整理時按照順序進行重新整理。
如果WITH後面跟了一個或多個COLUMN名稱，則物化視圖日誌中會包含這些列。

           

下面通過例子進行詳細說明：

SQL> create table t_rowid (id number, name varchar2(30), num number);
表已建立。

             
SQL> create materialized view log on t_rowid with rowid, sequence (name, num) including new values;
實體化視圖日誌已建立。

                
SQL> create table t_pk (id number primary key, name varchar2(30), num number);
表已建立。

                
SQL> create materialized view log on t_pk with primary key;
實體化視圖日誌已建立。

             
SQL> create type t_object as object (id number, name varchar2(30), num number);
     /
類型已建立
                

SQL> create table t_oid of t_object;
表已建立。

              
SQL> desc t_oid;
名稱                                      是否為空白? 類型
----------------------------------------- -------- ---------------
ID                                                 NUMBER
NAME                                               VARCHAR2(30)
NUM                                                NUMBER

                
SQL> create materialized view log on t_oid with object id;
實體化視圖日誌已建立。

        

建立環境後來看看物化視圖日誌中包含的欄位：
SQL> desc mlog$_t_rowid;
名稱                                      是否為空白? 類型
----------------------------------------- -------- -------------
NAME                                               VARCHAR2(30)
NUM                                                NUMBER
M_ROW$$                                            VARCHAR2(255)
SEQUENCE$$                                         NUMBER
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)
除了最基本的4列之外，由於指定了ROWID、SEQUENCE和NAME、NUM列，因此物化視圖日誌中包含了相對應的列。

                  
SQL> desc mlog$_t_pk;
名稱                                      是否為空白? 類型
----------------------------------------- -------- ------------
ID                                                 NUMBER
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)

            
對象表的物化視圖日誌建立後包含系統對象識別欄位。
一、主鍵列、ROWID列、OBJECT ID列、SEQUENCE列和建立物化視圖時指明的列。
主鍵、ROWID或OBJECT ID用來唯一表示物化視圖日誌中的記錄。
SEQUENCE會根據操作發生的順序對物化視圖日誌中的記錄編號。

          
建立物化視圖時指明的列會在物化視圖日誌中進行記錄。  

SQL> insert into t_pk values (1, 'a', 5);
已建立 1 行。

            
SQL> update t_pk set name = 'c' where id = 1;
已更新 1 行。

          
SQL> delete t_pk;
已刪除 1 行。

             
SQL> select id, dmltype$$ from mlog$_t_pk;
        ID D
---------- -
         1 I
         1 U
         1 D

           
SQL> insert into t_oid values (1, 'a', 5);
已建立 1 行。

          
SQL> update t_oid set name = 'c' where id = 1;
已更新 1 行。

           
SQL> delete t_oid;
已刪除 1 行。

              
SQL> select sys_nc_oid$, dmltype$$ from mlog$_t_oid;
SYS_NC_OID$                      D
-------------------------------- -
18DCFDE5D65B4D5A88602D6C09E5CE20 I
18DCFDE5D65B4D5A88602D6C09E5CE20 U
18DCFDE5D65B4D5A88602D6C09E5CE20 D

             
SQL> rollback;

回退已完成。

   

二、時間列
      當基本發生DML操作時，會記錄到物化視圖日誌中，這時指定的時間4000年1月1日0時0分0秒。如果物化視圖日誌供多個物化視圖使用，則一個物化視圖重新整理後會將它重新整理的記錄的時間更新為它重新整理的時間。
      下面建立快速重新整理的兩個物化視圖來示範時間列的變化。（只有建立快速重新整理的物化視圖才能使用物化視圖日誌，如果只建立一個物化視圖，則物化視圖重新整理完會將物化視圖日誌清除掉。

            

SQL> create materialized view mv_t_rowid refresh fast on commit as select name, count(*) from t_rowid group by name;
實體化視圖已建立。

             
SQL> create materialized view mv_t_rowid1 refresh fast as select name, count(*) from t_rowid group by name;
實體化視圖已建立。

              
SQL> insert into t_rowid values (1, 'a', 5);
已建立 1 行。

               
SQL> update t_rowid set name = 'c' where id = 1;
已更新 1 行。

               
SQL> delete t_rowid;
已刪除 1 行。

           
SQL> select snaptime$$ from mlog$_t_rowid;
SNAPTIME$$
-------------------
4000-01-01 00:00:00
4000-01-01 00:00:00
4000-01-01 00:00:00
4000-01-01 00:00:00

             
SQL> commit;
提交完成。

           
SQL> select snaptime$$ from mlog$_t_rowid;
SNAPTIME$$
-------------------
2012/5/23 15:41:41
2012/5/23 15:41:41
2012/5/23 15:41:41
2012/5/23 15:41:41
COMMIT後，物化視圖mv_t_rowid重新整理，將SNAPTIME$$列更新成自己的重新整理時間。

      

三、操作類型和新舊值
操作類型比較簡單：只包括I(INSERT)、D(DELETE)和U(UPDATE)三種。
新舊值也包括三種：O表示舊值（一般對應的操作時DELETE）、N表示新值（一般對應的操作是INSERT），還有一種U（對應UPDATE操作）。

SQL> insert into t_pk values (1, 'a', 5);
已建立 1 行。
       
SQL> insert into t_pk values (2, 'b', 7);
已建立 1 行。
          
SQL> insert into t_pk values (3, 'c', 9);
已建立 1 行。
             
SQL> update t_pk set name = 'c' where id = 1;
已更新 1 行。
            
SQL> update t_pk set id = 4 where id = 2;
已更新 1 行。
        
SQL> delete t_pk where id = 3;
已刪除 1 行。
            
SQL> select id, dmltype$$, old_new$$ from mlog$_t_pk;
        ID D O
---------- - -
         1 I N
         2 I N
         3 I N
         1 U U
         2 D O
         4 I N
         3 D O
已選擇7行。
         
開始是插入三條記錄，接著是UPDATE操作。需要注意，對於基於主鍵的物化視圖日誌，如果更新了主鍵，則UPDATE操作轉化為一條DELETE操作，一條INSERT操作。最後是DELETE操作。

SQL> drop materialized view log on t_rowid;
實體化視圖日誌已刪除。
         
SQL> create materialized view log on t_rowid with rowid, sequence (name, num) including new values;
實體化視圖日誌已建立。
           
SQL> insert into t_rowid values (1, 'a', 5);
已建立 1 行。
          
SQL> insert into t_rowid values (2, 'b', 7);
已建立 1 行。
         
SQL> insert into t_rowid values (3, 'c', 9);
已建立 1 行。
         
SQL> update t_rowid set name = 'c' where id = 1;
已更新 1 行。
        
SQL> update t_rowid set id = 4 where id = 2;
已更新 1 行。
        
SQL> delete t_rowid where id = 3;
已刪除 1 行。
            
SQL> select name, num, m_row$$, dmltype$$, old_new$$ from mlog$_t_rowid;
NAME              NUM M_ROW$$            D   O
---------- ---------- ------------------ - -
a                   5 AAACIDAAFAAAAD4AAC I N
b                   7 AAACIDAAFAAAAD4AAA I N
c                   9 AAACIDAAFAAAAD4AAB I N
a                   5 AAACIDAAFAAAAD4AAC U U
c                   5 AAACIDAAFAAAAD4AAC U N
b                   7 AAACIDAAFAAAAD4AAA U U
b                   7 AAACIDAAFAAAAD4AAA U N
c                   9 AAACIDAAFAAAAD4AAB D O

已選擇8行。

查詢結果和上面類似，唯一的區別是每條UPDATE操作都對應物化視圖日誌中的兩條記錄。一條對應UPDATE操作的原記錄DMLTYPE$$和OLD_NEW$$都為U，一條對應UPDATE操作後的新記錄，DMLTYPE$$為U，OLD_NEW$$為N。當建立物化視圖日誌時指出了INCLUDING NEW VALUES語句時，就會出現這種情況。

     

四、修改向量
      最後簡單討論一下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操作對應的CHANGE_VECTOR$$列為全FF。

         

SQL> insert into t_rowid values (1, 'a', 5);
已建立 1 行。
     
SQL> insert into t_rowid values (2, 'b', 7);
已建立 1 行。
            
SQL> insert into t_rowid values (3, 'c', 9);
已建立 1 行。
          
SQL> update t_rowid set name = 'c' where id = 1;
已更新 1 行。
          
SQL> update t_rowid set id = 4 where id = 2;
已更新 1 行。
          
SQL> update t_rowid set name = 'd', num = 11 where id = 3;
已更新 1 行。
         
SQL> delete t_rowid where id = 3;
已刪除 1 行。
 
SQL> select name, num, m_row$$, dmltype$$, old_new$$, change_vector$$ from mlog$_t_rowid;
 
可以看到，正如上面分析的，INSERT為FE，DELETE為00，對第一列的更新為02，第二列為04，第二列和第三列都更新為0C。需要注意，正常情況下，第一列會從02開始，但是如果對MLOG$表執行了TRUNCATE操作，或者重建了物化視圖日誌，則可能造成第一列開始位置發生位移。

SQL> insert into t_pk values (1, 'a', 5);
已建立 1 行。
         
SQL> insert into t_pk values (2, 'b', 7);
已建立 1 行。
         
SQL> insert into t_pk values (3, 'c', 9);
已建立 1 行。
           
SQL> update t_pk set name = 'c' where id = 1;
已更新 1 行。
           
SQL> update t_pk set id = 4 where id = 2;
已更新 1 行。
        
SQL> delete t_pk where id = 1;
已刪除 1 行。
         
SQL> commit
提交完成。 
      
SQL> select * from mlog$_t_pk;

這個結果和ROWID類型基本一致，不同的是，如果更新了主鍵，會將UPDATE操作在物化視圖日誌中記錄為一條DELETE和一條INSERT，不過這時INSERT對應的CHANGE_VECTOR$$的值是FF。

 SQL> insert into t_oid values (1, 'a', 5);
已建立 1 行。
        
SQL> update t_oid set name = 'c' where id = 1;
已更新 1 行。
        
SQL> update t_oid set id = 5 where id = 1;
已更新 1 行。
        
SQL> delete t_oid;
已刪除 1 行。
       
SQL> commit;
提交完成。
          
SQL> select * from mlog$_t_oid;

SQL> select name, segcollength from sys.col$ where obj# = (select object_id from user_objects where object_name ='T_OID');

NAME                           SEGCOLLENGTH
------------------------------ ------------
SYS_NC_OID$                        16
SYS_NC_ROWINFO$                1
ID                                          22
NAME                                     30
NUM                                       22

這個結果也和ROWID類型基本一致，需要注意的是，由於對象表包含兩個隱含列，因此ID不再是第一個欄位，而是第三個，因此對應的值是08。

SQL> create table t (
   col1 number, 
   col2 number, 
   col3 number, 
   col4 number, 
   col5 number, 
   col6 number, 
   col7 number, 
   col8 number, 
   col9 number, 
   col10 number, 
   col11 number, 
   col12 number 
);
表已建立。

SQL> create materialized view log on t with rowid;
實體化視圖日誌已建立。
         
SQL> insert into t values (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
已建立 1 行。
           
SQL> update t set col1 = 10;
已更新 1 行。
          
SQL> update t set col11 = 110;
已更新 1 行。
         
SQL> update t set col5 = 50, col12 = 120;
已更新 1 行。
         
SQL> delete t;
已刪除 1 行。
         
SQL> commit;
提交完成。
     
SQL> select * from mlog$_t;

最後看一個包含列數較多的例子，唯一需要注意的是，低位在左，高位在右。