oracle物化视图日志结构是怎样的

40次阅读

共计 7375 个字符，预计需要花费 19 分钟才能阅读完成。

本篇内容介绍了“oracle 物化视图日志结构是怎样的”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

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;