oracle物化視圖日志結構是怎樣的

本篇內容介紹了“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;

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

“oracle物化視圖日志結構是怎樣的”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！