Oracle物化视图获取增量数据

物化视图的快速刷新需要先构造物化视图日志，而物化视图日志中会记录表的dml操作，因此可以通过物化视图日志订阅Oracle增量。

1.物化视图日志名

物化视图日志名为MLOG$_表名。当表名长度超过20时，只取前20位；当出现截短后名称重复时，会自动在物化视图日志名后面添加数字。

2.物化视图日志结构

基本字段：
SNAPTIME$$：数据刷新的时间;当该物化视图日志仅供一张物化视图使用时，为4000-01-01 00:00:00；当其供多张物化视图使用时，该字段会记录dml操作的时间。

DMLTYPE$$： 表示DML操作类型，I表示INSERT，D表示DELETE，U表示UPDATE。

OLD_NEW$$：表示这个值是新值还是旧值。N表示新值，O表示旧值，U表示UPDATE操作。

CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。

特殊字段：

（1）构建物化视图日志时添加了rowid，因此物化视图日志中会包含字段：M_ROW$$：存储增量记录的ROWID。

 

create materialized view log on student with rowid including new values;

（2）构建物化视图日志时添加了primary key，因此物化视图日志中会包含主键字段

create materialized view log on student with primary key including new values;

（3）构建物化视图日志时添加了SEQUENCE，因此物化视图日志中会包含字段：SEQUENCE$$：每个操作的SEQUENCE号，从而保证刷新时按照顺序进行刷新。

CREATE MATERIALIZED VIEW LOG ON STUDENT WITH 
sequence ( C_ID, C_NAME ) including new VALUES;

（4）构建物化视图日志时添加了表的字段名，因此物化视图日志中会包含这些字段

CREATE MATERIALIZED VIEW LOG ON STUDENT WITH 
 C_ID, C_NAME  including new VALUES;

3.注意点

（1）基于主键的物化视图日志，如果更新主键时,DML操作会转为一条DELETE记录和一条INSERT记录。即更新主键操作被转换为先插入新记录，然后删除旧记录；
（2）当创建物化视图日志时包含INCLUDING NEW VALUES语句时，每条UPDATE操作会在物化视图日志中生成两条记录。一条对应UPDATE操作的原记录DMLTYPE$$和OLD_NEW$$都为U；一条对应UPDATE操作后的新记录，DMLTYPE$$为U，OLD_NEW$$为N；

4.通过Oracle物化视图日志实现Oracle增量抽取。

目标表结构：

CREATE TABLE STUDENT(
  C_ID NUMBER PRIMARY KEY,
  C_NAME VARCHAR(255) 
)

1）实现该功能所需SQL语句：
(1.1) 创建物化视图日志

CREATE MATERIALIZED VIEW LOG ON STUDENT WITH ROWID,
sequence ( C_ID, C_NAME ) including new VALUES;
(1.2) 获取增量记录

SELECT  *

FROM MLOG$_STUDENT

ORDER BY SEQUENCE$$;
(1.3) 提交增量数据后，删除已经提交的增量数据

 DELETE FROM MLOG$_STUDENT WHERE SEQUENCE$$ > lastID
(1.4) 增量完成后，删除物化视图日志

DROP MATERIALIZED VIEW LOG ON STUDENT;