物化视图日志简介

1、oracle物化视图日志结构      物化视图的快速刷新要求基本必须建立物化视图日志，这篇文章简单描述一下物化视图日志中各个字段的含义和用途。      物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号。      物化视图日志在建立时有多种选项：可以指定为ROWID、PRIMARY KEY和OBJECT ID几

2、种类型，同时还可以指定SEQUENCE或明确指定列名。上面这些情况产生的物化视图日志的结构都不相同。                 任何物化视图都会包括的4列：SNAPTIME$：用于表示刷新时间。DMLTYPE$：用于表示DML操作类型，I表示INSERT，D表示DELETE，U表示UPDATE。OLD_NEW$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U表示UPDATE操作。CHANGE_V

3、ECTOR$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。如果WITH后面跟了ROWID，则物化视图日志中会包含：M_ROW$：用来存储发生变化的记录的ROWID。如果WITH后面跟了PRIMARY KEY，则物化视图日志中会包含主键列。如果WITH后面跟了OBJECT ID，则物化视图日志中会包含：SYS_NC_OID$：用来记录每个变化对象的对象ID。如果WITH后面跟了SEQUENCE，则物化视图日子中会包含：SEQUENCE$：给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。如果WITH后面跟了一个或多个COLUMN名称，则物化视图日志中会包含这些列。 

4、;          下面通过例子进行详细说明：SQL> create table t_rowid (id number, name varchar2(30), num number);表已创建。             SQL> create materialized view log on t_rowid wit

5、h rowid, sequence (name, num) including new values;实体化视图日志已创建。                SQL> create table t_pk (id number primary key, name varchar2(30), num number);表已创建。       

6、0;        SQL> create materialized view log on t_pk with primary key;实体化视图日志已创建。             SQL> create type t_object as object (id number, name varchar2(30), num numbe

7、r);     /类型已创建                SQL> create table t_oid of t_object;表已创建。              SQL> desc t_oid;名称 

8、                                     是否为空? 类型- - -ID           &#

9、160;                                     NUMBERNAME           

10、60;                                   VARCHAR2(30)NUM             

11、;                                   NUMBER              

12、0; SQL> create materialized view log on t_oid with object id;实体化视图日志已创建。        建立环境后来看看物化视图日志中包含的字段：SQL> desc mlog$_t_rowid;名称                  

13、0;                   是否为空? 类型- - -NAME                            

14、0;                  VARCHAR2(30)NUM                              

15、                  NUMBERM_ROW$                               

16、;             VARCHAR2(255)SEQUENCE$                                  

17、       NUMBERSNAPTIME$                                         DATEDML

18、TYPE$                                          VARCHAR2(1)OLD_NEW$     

19、;                                     VARCHAR2(1)CHANGE_VECTOR$         

20、60;                          RAW(255)除了最基本的4列之外，由于指定了ROWID、SEQUENCE和NAME、NUM列，因此物化视图日志中包含了相对应的列。             &

21、#160;    SQL> desc mlog$_t_pk;名称                                      是否为空? 类型- - -ID &

22、#160;                                               NUMBERSNAPTIME$

23、60;                                        DATEDMLTYPE$        &#

24、160;                                 VARCHAR2(1)OLD_NEW$              

25、                            VARCHAR2(1)CHANGE_VECTOR$                  

26、0;                 RAW(255)            对象表的物化视图日志建立后包含系统对象标识列。一、主键列、ROWID列、OBJECT ID列、SEQUENCE列和建立物化视图时指明的列。主键、ROWID或OBJECT ID用来唯一表示物化视图日志中的记录。SEQUENCE会根据操作发生的顺

27、序对物化视图日志中的记录编号。          建立物化视图时指明的列会在物化视图日志中进行记录。  SQL> insert into t_pk values (1, 'a', 5);已创建 1 行。            SQL> update t_pk set name = 'c' where id = 1;

28、已更新 1 行。          SQL> delete t_pk;已删除 1 行。             SQL> select id, dmltype$ from mlog$_t_pk;        ID D- -     &#

29、160;   1 I         1 U         1 D           SQL> insert into t_oid values (1, 'a', 5);已创建 1 行。        &

30、#160; SQL> update t_oid set name = 'c' where id = 1;已更新 1 行。           SQL> delete t_oid;已删除 1 行。              SQL> select sys_nc_oid$, dmltype$ from mlo

31、g$_t_oid;SYS_NC_OID$                      D- -18DCFDE5D65B4D5A88602D6C09E5CE20 I18DCFDE5D65B4D5A88602D6C09E5CE20 U18DCFDE5D65B4D5A88602D6C09E5CE20 D        

32、;     SQL> rollback;回退已完成。   二、时间列      当基本发生DML操作时，会记录到物化视图日志中，这时指定的时间4000年1月1日0时0分0秒。如果物化视图日志供多个物化视图使用，则一个物化视图刷新后会将它刷新的记录的时间更新为它刷新的时间。      下面建立快速刷新的两个物化视图来演示时间列的变化。（只有建立快速刷新的物化视图才能使用物化视图日志，如果只建立一个物化视图，则物化

33、视图刷新完会将物化视图日志清除掉。            SQL> create materialized view mv_t_rowid refresh fast on commit as select name, count(*) from t_rowid group by name;实体化视图已创建。             SQL&g

34、t; 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 行。      

35、60;        SQL> update t_rowid set name = 'c' where id = 1;已更新 1 行。               SQL> delete t_rowid;已删除 1 行。          &

36、#160;SQL> select snaptime$ from mlog$_t_rowid;SNAPTIME$-4000-01-01 00:00:004000-01-01 00:00:004000-01-01 00:00:004000-01-01 00:00:00             SQL> commit;提交完成。           S

37、QL> select snaptime$ from mlog$_t_rowid;SNAPTIME$-2012/5/23 15:41:412012/5/23 15:41:412012/5/23 15:41:412012/5/23 15:41:41COMMIT后，物化视图mv_t_rowid刷新，将SNAPTIME$列更新成自己的刷新时间。      三、操作类型和新旧值操作类型比较简单：只包括I(INSERT)、D(DELETE)和U(UPDATE)三种。新旧值也包括三种：O表示旧值（一般对应的操作时DELETE）、N表示新值（一

38、般对应的操作是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

39、9;, 9);已创建 1 行。             SQL> update t_pk set name = 'c' where id = 1;已更新 1 行。            SQL> update t_pk set id = 4 where id = 2;已更新 1 行。   &

40、#160;    SQL> delete t_pk where id = 3;已删除 1 行。            SQL> select id, dmltype$, old_new$ from mlog$_t_pk;        ID D O- - -         1

41、 I N         2 I N         3 I N         1 U U         2 D O         4 I N    &#

42、160;    3 D O已选择7行。         开始是插入三条记录，接着是UPDATE操作。需要注意，对于基于主键的物化视图日志，如果更新了主键，则UPDATE操作转化为一条DELETE操作，一条INSERT操作。最后是DELETE操作。SQL> drop materialized view log on t_rowid;实体化视图日志已删除。         SQL>

43、; 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&

44、gt; 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 行。

45、60;       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$, dm

46、ltype$, old_new$ from mlog$_t_rowid;NAME              NUM M_ROW$            D   O- - - - -a             &

47、#160;     5 AAACIDAAFAAAAD4AAC I Nb                   7 AAACIDAAFAAAAD4AAA I Nc                   9

48、AAACIDAAFAAAAD4AAB I Na                   5 AAACIDAAFAAAAD4AAC U Uc                   5 AAACIDAAFAAAAD4AAC U Nb 

49、60;                 7 AAACIDAAFAAAAD4AAA U Ub                   7 AAACIDAAFAAAAD4AAA U Nc       

50、;            9 AAACIDAAFAAAAD4AAB D O已选择8行。查询结果和上面类似，唯一的区别是每条UPDATE操作都对应物化视图日志中的两条记录。一条对应UPDATE操作的原记录DMLTYPE$和OLD_NEW$都为U，一条对应UPDATE操作后的新记录，DMLTYPE$为U，OLD_NEW$为N。当建立物化视图日志时指出了INCLUDING NEW VALUES语句时，就会出现这种情况。     四、修改矢量 

51、     最后简单讨论一下CHANGE_VECTOR$列。      INSERT和DELETE操作都是记录集的，即INSERT和DELETE会影响整条记录。而UPDATE操作是字段集的，UPDATE操作可能会更新整条记录的所有字段，也可能只更新个别字段。      无论从性能上考虑还是从数据的一致性上考虑，物化视图刷新时都应该是基于字段集。Oracle就是通过CHANGE_VECTOR$列来记录每条记录发生变化的字段包括哪些。  &#

52、160;   基于主键、ROWID和OBJECT ID的物化视图日志在CHANGE_VECTOR$上略有不同，但是总体设计的思路是一致的。           CHANGE_VECTOR$列是RAW类型，其实Oracle采用的方式就是用每个BIT位去映射一个列。      比如：第一列被更新设置为02，即00000010。第二列设置为04，即00000100，第三列设置为08，即00001000。当第一列和第二列同时被更新，则

53、设置为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如果基表列数较多，而存在高位的话，所有的高位都为

54、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 行。   

55、         SQL> insert into t_rowid values (3, 'c', 9);已创建 1 行。          SQL> update t_rowid set name = 'c' where id = 1;已更新 1 行。         &#

56、160;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 行。 

57、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 行。 

58、        SQL> insert into t_pk values (2, 'b', 7);已创建 1 行。         SQL> insert into t_pk values (3, 'c', 9);已创建 1 行。           SQL> upda

59、te 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 行。       

60、;  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 行。    

61、    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 行。     

62、60; 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      

63、0;                    SEGCOLLENGTH- -SYS_NC_OID$                        16SYS_NC_ROWI

64、NFO$                1ID                                 

65、;         22NAME                                     30NUM   

66、0;                                   22这个结果也和ROWID类型基本一致，需要注意的是，由于对象表包含两个隐含列，因此ID不再是第一个字段，而是第三个，因此对应的值是08。SQL> create table t (

67、   col1 number,    col2 number,    col3 number,    col4 number,    col5 number,    col6 number,    col7 number,    col8 number,    col9 number,    col10 nu

68、mber,    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 行。    

69、       SQL> update t set col1 = 10;已更新 1 行。          SQL> update t set col11 = 110;已更新 1 行。         SQL> update t set col5 = 50, col12 = 120;已更新 1 行。  

70、       SQL> delete t;已删除 1 行。         SQL> commit;提交完成。     SQL> select * from mlog$_t;最后看一个包含列数较多的例子，唯一需要注意的是，低位在左，高位在右。-  Oracle如何根据物化视图日志快速刷新物化视图Oracle物化视图的快速刷新机制是通过物化视图日志完成

71、的。Oracle如何通过一个物化视图日志就可以支持多个物化视图的快速刷新呢，本文简单的描述一下刷新的原理。         首先，看一下物化视图的结构：SQL> create table t(id number, name varchar2(30), num number);表已创建。           SQL> create materialized view log on t w

72、ith rowid, sequence(id, name) including new values;实体化视图日志已创建。                SQL> desc mlog$_tID和NAME是建立物化视图日志时指定的基表中的列，它们记录每次DML操作对应的ID和NAME的值。M_ROW$保存基表的ROWID信息，根据M_ROW$中的信息可以定位到发生DML操作的记录。SEQUENCE$根据DML操作发生的顺序记录序列

73、的编号，当刷新时，根据SEQUENCE$中的顺序就可以和基表中的执行顺序保持一致。SNAPTIME$列记录了刷新操作的时间。DMLTYPE$的记录值I、U和D，表示操作是INSERT、UPDATE还是DELETE。OLD_NEW$表示物化视图日志中保存的信息是DML操作之前的值（旧值）还是DML操作之后的值（新值）。除了O和N这两种类型外，对于UPDATE操作，还可能表示为U。CHANGE_VECTOR$记录DML操作发生在那个或那几个字段上。            

74、0;    有关物化视图日志结构的详细描述，可以参考文档：物化视图日志结构：        根据上面的描述，可以发现，当刷新物化视图时，只需要根据SEQUENCE$列给出的顺序，通过M_ROW$定位到基表的记录，如果是UPDATE操作，通过CHANGE_VECTOR$定位到字段，然后根据基表中的数据重复执行DML操作。        如果物化视图日志只针对一个物化视图，那么刷新过程就是这么简单，还

75、需要做的不过是在刷新之后将物化视图日志清除掉。              但是，Oracle的物化视图日志是可以同时支持多个物化视图的快速刷新的，也就是说，物化视图在刷新时还必须判断哪些物化视图日志记录是当前物化视图刷新需要的，哪些是不需要的。而且，物化视图还必须确定，在刷新物化视图后，物化视图日志中哪些记录是需要清除的，哪些是不需要清除的。         

76、0;       回顾一下物化视图日志的结构，发现只剩下一个SHAPTIME$列，那么Oracle如何仅通过这一列就完成了对多个物化视图的支持呢？下面建立一个小例子，通过例子来进行说明。            使用上文中建立的表和物化视图日志，下面对这个表建立三个快速刷新的物化视图。          

77、60;     SQL> create materialized view mv_t_id refresh fast as select id, count(*) from t group by id;实体化视图已创建。              SQL> create materialized view mv_t_name refresh fast as select name, count(

78、*) from t group by name;实体化视图已创建。             SQL> create materialized view mv_t_id_name refresh fast as select id, name, count(*) from t group by id, name;实体化视图已创建。           &

79、#160;SQL> insert into t values (1, 'a', 2);已创建 1 行。              SQL> insert into t values (1, 'b', 3);已创建 1 行。               SQL>

80、 insert into t values (2, 'a', 5);已创建 1 行。            SQL> insert into t values (3, 'b', 7);已创建 1 行。            SQL> update t set name = 'c' where id

81、= 3;已更新 1 行。              SQL> delete t where id = 2;已删除 1 行。            SQL> commit;提交完成。            SQ

82、L> select id, name, m_row$, snaptime$, dmltype$ from mlog$_t;当发生了DML操作后，物化视图日志中的SNAPTIME$列保持的值是4000-01-01 00:00:00。这个值表示这条记录还没有被任何物化视图刷新过。第一个刷新这些记录的物化视图会将SNAPTIME$的值更新为物化视图当前的刷新时间。         SQL> exec dbms_mview.refresh('MV_T_ID');PL/SQL 过程已成功

83、完成。         SQL> select id, name, m_row$, snaptime$, dmltype$ from mlog$_t;                        Oracle根据数据字典中的信息可以知道表T上建立了三个物化视图，因此，MV

84、_T_ID刷新完之后，不会删除物化视图记录。Oracle的数据字典中还保存着每个物化视图上次刷新的时间和当前的刷新状态。SQL> select name, last_refresh from user_mview_refresh_times;SQL> select mview_name, last_refresh_date, staleness from user_mviews;这些视图中记录了每个物化视图上次执行刷新操作的时间，并且给出每个物化视图中的数据是否是和基表同步的。由于MV_T_ID刚刚进行了刷新，因此状态是FRESH，而另外两个由于在刷新（建立）之后，基表又进行了DML操作，因此状态为NEEDS_COMPILE。如果这时对基表进行DM