物化视图日志简介.docx

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 tablet_rowid(id number, name varchar2(30), num number);表已创建。SQL create materialized view log ont_rowidwith rowid, sequence (name, num) including new values;实体化视图日志已创建。SQL create tablet_pk(id number primary key, name varchar2(30), num number);表已创建。SQL create materialized view log ont_pkwith primary key;实体化视图日志已创建。SQL create typet_objectas object (id number, name varchar2(30), num number); /类型已创建SQL create tablet_oidoft_object;表已创建。SQL desc t_oid;名称 是否为空? 类型- - -ID NUMBERNAME VARCHAR2(30)NUM NUMBERSQL create materialized view log ont_oidwith object id;实体化视图日志已创建。建立环境后来看看物化视图日志中包含的字段：SQL desc mlog$_t_rowid;名称 是否为空? 类型- - -NAME VARCHAR2(30)NUM NUMBERM_ROW$ VARCHAR2(255)SEQUENCE$ NUMBERSNAPTIME$ DATEDMLTYPE$ VARCHAR2(1)OLD_NEW$ VARCHAR2(1)CHANGE_VECTOR$ RAW(255)除了最基本的4列之外，由于指定了ROWID、SEQUENCE和NAME、NUM列，因此物化视图日志中包含了相对应的列。SQL desc mlog$_t_pk;名称 是否为空? 类型- - -ID NUMBERSNAPTIME$ DATEDMLTYPE$ 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 DSQL 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 I18DCFDE5D65B4D5A88602D6C09E5CE20 U18DCFDE5D65B4D5A88602D6C09E5CE20 DSQL 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:004000-01-01 00:00:004000-01-01 00:00:004000-01-01 00:00:00SQL commit;提交完成。SQL 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表示新值（一般对应的操作是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 Nb 7 AAACIDAAFAAAAD4AAA I Nc 9 AAACIDAAFAAAAD4AAB I Na 5 AAACIDAAFAAAAD4AAC U Uc 5 AAACIDAAFAAAAD4AAC U Nb 7 AAACIDAAFAAAAD4AAA U Ub 7 AAACIDAAFAAAAD4AAA U Nc 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$16SYS_NC_ROWINFO$1ID 22NAME 30NUM 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如何根据物化视图日志快速刷新物化视图Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle如何通过一个物化视图日志就可以支持多个物化视图的快速刷新呢，本文简单的描述一下刷新的原理。首先，看一下物化视图的结构：SQL create table t(id number, name varchar2(30), num number);表已创建。SQL create materialized view log on t with rowid, sequence(id, name) including new values;实体化视图日志已创建。SQL desc mlog$_tID和NAME是建立物化视图日志时指定的基表中的列，它们记录每次DML操作对应的ID和NAME的值。M_ROW$保存基表的ROWID信息，根据M_ROW$中的信息可以定位到发生DML操作的记录。SEQUENCE$根据DML操作发生的顺序记录序列的编号，当刷新时，根据SEQUENCE$中的顺序就可以和基表中的执行顺序保持一致。SNAPTIME$列记录了刷新操作的时间。DMLTYPE$的记录值I、U和D，表示操作是INSERT、UPDATE还是DELETE。OLD_NEW$表示物化视图日志中保存的信息是DML操作之前的值（旧值）还是DML操作之后的值（新值）。除了O和N这两种类型外，对于UPDATE操作，还可能表示为U。CHANGE_VECTOR$记录DML操作发生在那个或那几个字段上。有关物化视图日志结构的详细描述，可以参考文档：物化视图日志结构：/post/468/20498根据上面的描述，可以发现，当刷新物化视图时，只需要根据SEQUENCE$列给出的顺序，通过M_ROW$定位到基表的记录，如果是UPDATE操作，通过CHANGE_VECTOR$定位到字段，然后根据基表中的数据重复执行DML操作。如果物化视图日志只针对一个物化视图，那么刷新过程就是这么简单，还需要做的不过是在刷新之后将物化视图日志清除掉。但是，Oracle的物化视图日志是可以同时支持多个物化视图的快速刷新的，也就是说，物化视图在刷新时还必须判断哪些物化视图日志记录是当前物化视图刷新需要的，哪些是不需要的。而且，物化视图还必须确定，在刷新物化视图后，物化视图日志中哪些记录是需要清除的，哪些是不需要清除的。回顾一下物化视图日志的结构，发现只剩下一个SHAPTIME$列，那么Oracle如何仅通过这一列就完成了对多个物化视图的支持呢？下面建立一个小例子，通过例子来进行说明。使用上文中建立的表和物化视图日志，下面对这个表建立三个快速刷新的物化视图。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(*) 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;实体化视图已创建。SQL insert into t values (1, a, 2);已创建 1 行。SQL insert into t values (1, b, 3);已创建 1 行。SQL 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 = 3;已更新 1 行。SQL delete t where id = 2;已删除 1 行。SQL commit;提交完成。SQL 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 过程已成功完成。SQL select id, name, m_row$, snaptime$, dmltype$ from mlog$_t; Oracle根据数据字典中的信息可以知道表T上建立了三个物化视图，因此，MV_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。如果这时对基表进行DML操作，则MV_T_ID的状态也会变为NEEDS_COMPILE。SQL insert into t values (4, d, 10);已创建 1 行。SQL commit;提交完成。SQL select id, name, m_row$, snaptime$, dmltype$ from mlog$_t;SQL select mview_name, last_refresh_date, staleness from user_mviews;下面刷新物化视图MV_T_ID_NAME，刷新操作的判断依据是，只刷新SNAPTIME$列大于当前物化视图的LAST_REFRESH_DATE的记录，由于物化视图日志中所有记录的SNAPTIME$的值都比物化视图MV_T_ID_NAME上次刷新的时间点大，因此会刷新所有记录。对于SNAPTIME$列的值是4000-01-01 00:00:00的记录，物化视图会把SNAPTIME$列的值更新为当前刷新时间，对于那些已经被更新过的SNAPTIME$列，则保持原值。SQL exec dbms_mview.refresh(MV_T_ID_NAME)PL/SQL 过程已成功完成。SQL select id, name, m_row$, snaptime$, dmltype$ from mlog$_t;SQL select mview_name, last_refresh_date, staleness from user_mviews;如果这时再次刷新物化视图MV_T_ID，则只有ID=4的这条记录的SNAPTIME$的时间点大于MV_T_ID上次刷新的时间点，因此，只刷新这一条记录，且不会改变SNAPTIME$的值。SQL exec dbms_mview.refresh(MV_T_ID)PL/SQL 过程已成功完成。SQL select id, na