OCM考试经验--物化视图日志详解

---======================================

--=  OCM考试经验

--=      物化视图日志

--=      详解

--=======================================

一、概念

物化视图的快速刷新要求基本必须建立物化视图日志，这篇文章简单描述一下物化视图日志中各个字段的含义和用途。

      物化视图日志的名称为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参数
如果WITH后面跟了ROWID，则物化视图日志中会包含：M_ROW$$：用来存储发生变化的记录的ROWID。
如果WITH后面跟了PRIMARY KEY，则物化视图日志中会包含主键列。
如果WITH后面跟了OBJECT ID，则物化视图日志中会包含：SYS_NC_OID$：用来记录每个变化对象的对象ID。用于对象类

---附加参数
如果WITH后面跟了SEQUENCE，则物化视图日子中会包含：SEQUENCE$$：给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。
如果WITH后面跟了一个或多个COLUMN名称，则物化视图日志中会包含这些列。  一般多column在sequence组合 sequence(column1,column2...)

--including new values参数  ：该参数影响DMLTYPE$$OLD_NEW$$ 的记录

    --不加入该参数 对于基于主键的物化视图日志，如果更新了主键，则UPDATE操作转化为一条DELETE操作，一条INSERT操作

    --加入该参数 任何UPDATE操作都转化为一条DELETE操作，一条INSERT操作

下面通过例子进行详细说明：

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;  需要注意，对于基于主键的物化视图日志，如果更新了主键，则UPDATE操作转化为一条DELETE操作，一条INSERT操作
已更新 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          需要注意，对于基于主键的物化视图日志，如果更新了主键，则UPDATE操作转化为一条DELETE操作，一条INSERT操作

         3 D O
已选择7行。
         
开始是插入三条记录，接着是UPDATE操作最后是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;

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