【物化视图】几种物化视图日志分析

物化视图日志在建立时有多种选项：ROWID、PRIMARY KEY和OBJECT ID三种类型，同时还可以指定SEQUENCE或明确指定列名。
上面这些情况产生的物化视图日志的结构都不相同。
所有物化视图都会包括的列：
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名称，则物化视图日志中会包含这些列。

yang@rac1>create table yang_rowid (id number,name varchar2(30),val number);
Table created.

yang@rac1>create materialized view log on yang_rowid with rowid ,sequence (name,val);
Materialized view log created.

yang@rac1>create table yang_pk (id number primary key ,name varchar2(30) ,val number);
Table created.

yang@rac1>create materialized view log on yang_pk with primary key;
Materialized view log created.

yang@rac1>create type yang_object as  object(id number ,name varchar2(30) ,val number);
  2  /
Type created.
yang@rac1>create table yang_oid of yang_object;
Table created.
yang@rac1>create materialized view log on yang_oid with object id;
Materialized view log created.

yang@rac1>desc mlog$_yang_rowid
 Name                 Type
 ------------------   ------------
 NAME                 VARCHAR2(30)
 VAL                  NUMBER
 M_ROW$$              VARCHAR2(255)
 SEQUENCE$$           NUMBER
 SNAPTIME$$           DATE
 DMLTYPE$$            VARCHAR2(1)
 OLD_NEW$$            VARCHAR2(1)
 CHANGE_VECTOR$$      RAW(255)
 XID$$                NUMBER

yang@rac1>desc mlog$_yang_pk
 Name                 Type
 ------------------- -------
 ID                   NUMBER
 SNAPTIME$$           DATE
 DMLTYPE$$            VARCHAR2(1)
 OLD_NEW$$            VARCHAR2(1)
 CHANGE_VECTOR$$      RAW(255)
 XID$$                NUMBER

yang@rac1>desc mlog$_yang_oid
 Name               Type
 ---------------  ---------
 SYS_NC_OID$        RAW(16)
 SNAPTIME$$         DATE
 DMLTYPE$$          VARCHAR2(1)
 OLD_NEW$$          VARCHAR2(1)
 CHANGE_VECTOR$$    RAW(255)
 XID$$              NUMBER
对象表的物化视图日志建立后包含系统对象标识列。
一、主键列、ROWID列、OBJECT ID列、SEQUENCE列和建立物化视图时指明的列。
主键、ROWID或OBJECT ID用来唯一表示物化视图日志中的记录。
SEQUENCE会根据操作发生的顺序对物化视图日志中的记录编号。
建立物化视图时指明的列会在物化视图日志中进行记录。
yang@rac1>select name,val,m_row$$,sequence$$,dmltype$$ from mlog$_yang_rowid；
NAME                                  VAL M_ROW$$                   SEQUENCE$$ D
------------------------------ ---------- ------------------------- ---------- -
s                                       1 AAAgNtAAIAACaJcAAA                 1 I
s                                       1 AAAgNtAAIAACaJcAAA                 2 U
lily                                    1 AAAgNtAAIAACaJcAAA                 4 D

yang@rac1>insert into yang_pk values (1,'ll',2);
1 row created.
yang@rac1>update yang_pk set name='lily' where id=1;
1 row updated.
yang@rac1>delete yang_pk;
1 row deleted.
yang@rac1>select id ,dmltype$$ from mlog$_yang_pk;
ID D
--- -
1 I --insert
1 U --update
1 D --delete
yang@rac1>select id ,dmltype$$,OLD_NEW$$ from mlog$_yang_pk;
ID D O
-- - -
1  I N --new
1  U U --update
1  D O --old
--时间列
当基本发生DML操作时，会记录到物化视图日志中，这时指定的时间4000年1月1日0时0分0秒。如果物化视图日志供多个物化视图使用，则一个物化视图刷新后会将它刷新的记录的时间更新为它刷新的时间。
下面建立快速刷新的两个物化视图来演示时间列的变化。
（只有建立快速刷新的物化视图才能使用物化视图日志，如果只建立一个物化视图，则物化视图刷新完会将物化视图日志清除掉。）
yang@rac1>create materialized view mv_yang_rowid refresh fast on commit with rowid as
  2      select name, val from yang_rowid;
Materialized view created.

yang@rac1>insert into yang_rowid values (1,'lily',2);
1 row created.
yang@rac1>update yang_rowid set name='yang' where id=1;
2 rows updated.
yang@rac1>delete yang_rowid;
2 rows deleted.
yang@rac1>select snaptime$$ from mlog$_yang_rowid;
SNAPTIME$$
-----------------------------
Jan 01 4000 00:00:00
Jan 01 4000 00:00:00
Jan 01 4000 00:00:00
Jan 01 4000 00:00:00
Jan 01 4000 00:00:00

yang@rac1>commit;
Commit complete.
yang@rac1>select snaptime$$ from mlog$_yang_rowid;
no rows selected

yang@rac1>select * from mv_yang_rowid;
no rows selected
yang@rac1>create materialized view mv_yang_rowid2 refresh fast  with rowid as
  2   select name, val from yang_rowid;
Materialized view created.
yang@rac1>insert into yang_rowid values (1,'lily',2);
1 row created.
yang@rac1>update yang_rowid set name='yang' where id=1;
1 row updated.
yang@rac1>select snaptime$$ from mlog$_yang_rowid;
SNAPTIME$$
-----------------------------
Jan 01 4000 00:00:00
Jan 01 4000 00:00:00
--问题出现了。就是提交时日期没有更新，仍然是初始值。
yang@rac1>commit;
Commit complete.

yang@rac1>select snaptime$$ from mlog$_yang_rowid;
SNAPTIME$$
-----------------------------
Jan 01 4000 00:00:00
Jan 01 4000 00:00:00
==
DMLTYPE$$ 的值分为： U ,I ,D 分别为更新，插入，删除。
OLD_NEW$$ 的值分为：N（EW）表示新值，O（LD）表示旧值，U表示UPDATE操作。
yang@rac1>insert into yang_rowid values (1,'lily',2);
1 row created.
yang@rac1>insert into yang_rowid values (2,'lily',1);
1 row created.
yang@rac1>insert into yang_rowid values (3,'lily',1);
1 row created.
yang@rac1>update yang_rowid set name='yang' where id=1;
1 row updated.
yang@rac1>delete yang_rowid where id=2;
1 row deleted.
yang@rac1>select name,val, m_row$$, dmltype$$, old_new$$ from mlog$_yang_rowid;
NAME     VAL M_ROW$$                  D O
-------- ---------- ----------------- - -
yang      2 AAAgNtAAIAACaJcAAA        D O
yang      2 AAAgNtAAIAACaJcAAB        D O
yang      2 AAAgNtAAIAACaJcAAC        D O
lily      2 AAAgNtAAIAACaJcAAD        D O
lily      2 AAAgNtAAIAACaJcAAE        I N
lily      1 AAAgNtAAIAACaJcAAF        I N
lily      1 AAAgNtAAIAACaJcAAG        I N
lily      2 AAAgNtAAIAACaJcAAE        U U
lily      1 AAAgNtAAIAACaJcAAF        D O

9 rows selected.
yang@rac1>create materialized view log on yang_rowid with rowid, sequence (name, val) including new values;
Materialized view log created.
yang@rac1>insert into yang_rowid values (1,'lily',1);
1 row created.
yang@rac1>insert into yang_rowid values (2,'lily',2);
1 row created.
yang@rac1>insert into yang_rowid values (3,'lily',3);
1 row created.
yang@rac1>update yang_rowid set name='yang' where id=1;
2 rows updated.
yang@rac1>delete yang_rowid where id=2;
1 row deleted.
yang@rac1>select name,val, m_row$$, dmltype$$, old_new$$,change_vector$$ from mlog$_yang_rowid;
NAME                                  VAL M_ROW$$                   D O CHANGE_VECTOR$$
------------------------------ ---------- ------------------------- - - --------------
lily                                    1 AAAgNtAAIAACaJcAAK        I N FE
lily                                    2 AAAgNtAAIAACaJcAAA        I N FE
lily                                    3 AAAgNtAAIAACaJcAAB        I N FE
yang                                    1 AAAgNtAAIAACaJcAAH        U U 00
yang                                    1 AAAgNtAAIAACaJcAAH        U N 00
lily                                    1 AAAgNtAAIAACaJcAAK        U U 04
yang                                    1 AAAgNtAAIAACaJcAAK        U N 04
lily                                    2 AAAgNtAAIAACaJcAAA        D O 00

8 rows selected.
当物化视图日志是含有关键字 including new values时，每一条对应UPDATE操作的原记录DMLTYPE$$和OLD_NEW$$都为U，
===更改向量Oracle是通过CHANGE_VECTOR$$列来记录每条记录发生变化的字段的。
CHANGE_VECTOR$$列是RAW类型，其实Oracle采用的方式就是用每个BIT位去映射一个列。
插入操作显示为：FE,
删除显示为：OO
更新操作则根据更新字段的位置而显示不同的值。比如说更新表yang_PK 的第二个字段name，显示00000100 为04
而当更新主键时 update操作分解为一个删除操作(表示为OO)和插入操作(表示为FF)
yang@rac1>insert into yang_pk values(2,'yang',2);
1 row created.
yang@rac1>insert into yang_pk values(3,'yang',3);
1 row created.
yang@rac1>update yang_pk set name='lily' where id=3;
1 row updated.
yang@rac1>update yang_pk set id=4 where id=3;
1 row updated.
yang@rac1>select id,dmltype$$, old_new$$,change_vector$$ from mlog$_yang_pk;
        ID D O CHANGE_VECTOR$$
---------- - - ---------------
         1 I N FE
         2 I N FE
         3 I N FE
         3 U U 04
         3 D O 00
         4 I N FF
6 rows selected.
--对于object id 日志：
yang@rac1>select object_id,dmltype$$, old_new$$,change_vector$$ from mlog$_yang_oid;
no rows selected

yang@rac1>insert into yang_oid values (1,'lily',1);
1 row created.
yang@rac1>insert into yang_oid values (2,'lily',2);
1 row created.
yang@rac1>insert into yang_oid values (3,'lily',3);
1 row created.
yang@rac1>update yang_oid set name='yang',val=5 where id=2;
1 row updated.
yang@rac1>delete yang_oid where id=3;
1 row deleted.
yang@rac1>select object_id,dmltype$$, old_new$$,change_vector$$ from mlog$_yang_oid;

OBJECT_ID                        D O CHANGE_VECTOR$$
-------------------------------- - - ----------------------------------------
A09F6E50AAD2008DE040010A6F9704FB I N FE
A09F6E50AAD3008DE040010A6F9704FB I N FE
A09F6E50AAD4008DE040010A6F9704FB I N FE
A09F6E50AAD3008DE040010A6F9704FB U U 30  --00110000 表示同时更新了第四列和第五列。为什么不是第二和第三呢？下面给解释
A09F6E50AAD4008DE040010A6F9704FB D O 00
yang@rac1>update yang_oid set name='yang',val=5 ,id=4 where id=2;
1 row updated.
yang@rac1>select object_id,dmltype$$, old_new$$,change_vector$$ from mlog$_yang_oid;

OBJECT_ID                        D O CHANGE_VECTOR$$
-------------------------------- - - ----------------------------------------
A09F6E50AAD2008DE040010A6F9704FB I N FE
A09F6E50AAD3008DE040010A6F9704FB I N FE
A09F6E50AAD4008DE040010A6F9704FB I N FE
A09F6E50AAD3008DE040010A6F9704FB U U 30
A09F6E50AAD4008DE040010A6F9704FB D O 00
A09F6E50AAD3008DE040010A6F9704FB U U 38
6 rows selected.
从下面的查询可知由于有两个隐含列，所以name，val 列分别为第4，5列了。即为
00110000
yang@rac1>select name, segcollength from sys.col$ where obj# =
  2         (select object_id from user_objects where object_name = 'YANG_OID');

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

yang@rac1>update yang_pk set name='yang',val=5 where id=6;
1 row updated.
yang@rac1>select id,dmltype$$, old_new$$,change_vector$$ from mlog$_yang_pk;
        ID D O CHANGE_VECTOR$$
---------- - - ----------------------------------------
         1 I N FE
         2 I N FE
         3 I N FE
         3 U U 04
         3 D O 00
         4 I N FF
         2 D O 00
         6 I N FF
         6 U U 0C  ==00001100
9 rows selected.
可以看到，正如上面分析的，INSERT为FE，DELETE为00，对第一列的更新为02，第二列为04，第二列和第三列都更新为0C。更新了主键时update被分解。
yang@rac1>update yang_pk set name='yang',val=5 ,id=2 where id=6;
1 row updated.
yang@rac1>select id,dmltype$$, old_new$$,change_vector$$ from mlog$_yang_pk;
        ID D O CHANGE_VECTOR$$
---------- - - ----------------
         1 I N FE
         2 I N FE
         3 I N FE
         3 U U 04
         3 D O 00
         4 I N FF
         2 D O 00
         6 I N FF
         6 U U 0C --第2，3列被更新。
         6 D O 00 --update主键操作被分解为删除，插入操作。
         2 I N FF
11 rows selected.