在MV上建立触发器实验

用户1148526

发布于 2019-05-25 19:43:50

4530

发布于 2019-05-25 19:43:50

文章被收录于专栏：Hadoop数据仓库Hadoop数据仓库

-- 建立MV测试表
CREATE TABLE tbl1
(
a NUMBER,
b VARCHAR2 (20)
);
CREATE UNIQUE INDEX tbl1_pk ON tbl1 (a);
ALTER TABLE tbl1 ADD (CONSTRAINT tbl1_pl PRIMARY KEY(a));
-- 建立MV日志，单一表聚合视图的快速刷新需要指定including new values子句
CREATE MATERIALIZED VIEW LOG ON tbl1 INCLUDING NEW VALUES;
-- 建立MV
CREATE MATERIALIZED VIEW mv_tbl1
BUILD IMMEDIATE
REFRESH FAST
START WITH TO_DATE('2013-06-01 08:00:00','yyyy-mm-dd hh24:mi:ss')
NEXT SYSDATE + 1/24
AS
SELECT * FROM tbl1;
-- 建立trigger测试表
CREATE TABLE mv_tbl1_tri
(
a NUMBER,
b VARCHAR (20),
c VARCHAR (20)
);
-- 建立trigger
CREATE OR REPLACE TRIGGER tri_mv
AFTER DELETE OR INSERT OR UPDATE
ON mv_tbl1
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
CASE
WHEN INSERTING THEN
INSERT INTO mv_tbl1_tri VALUES (:new.a, :new.b, 'insert');
WHEN UPDATING THEN
INSERT INTO mv_tbl1_tri VALUES (:new.a, :new.b, 'update');
WHEN DELETING THEN
INSERT INTO mv_tbl1_tri VALUES (:old.a, :old.b, 'delete');
END CASE;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END tri_mv;
/
-- 新增MV测试表数据
INSERT INTO tbl1 VALUES (1, '测试数据1');
INSERT INTO tbl1 VALUES (2, '测试数据2');
INSERT INTO tbl1 VALUES (3, '测试数据3');
COMMIT;
SELECT * FROM tbl1;
SELECT * FROM mlog$_tbl1; -- 生成3条“I”MV日志记录
-- 手工刷新MV
EXEC dbms_mview.refresh('mv_tbl1'); -- 刷新MV，触发3条insert，清空MV日志
SELECT * FROM mlog$_tbl1;
-- 检查trigger测试表
SELECT * FROM mv_tbl1_tri;
-- 修改MV测试表数据
UPDATE tbl1
SET a = 13
WHERE a = 3;
UPDATE tbl1
SET a = 3
WHERE a = 13;
UPDATE tbl1
SET a = 13
WHERE a = 3;
UPDATE tbl1
SET a = 3
WHERE a = 13;
UPDATE tbl1
SET a = 13
WHERE a = 3;
UPDATE tbl1
SET a = 13
WHERE a = 13;
UPDATE tbl1
SET a = 13
WHERE a = 13;
UPDATE tbl1
SET b = '测试数据13'
WHERE a = 13;
UPDATE tbl1
SET b = '测试数据3'
WHERE a = 13;
UPDATE tbl1
SET b = '测试数据13';
COMMIT;
SELECT * FROM tbl1;
SELECT * FROM mlog$_tbl1; -- 对每条记录的update生成2条MV日志记录：当主键发生改变时，记录一对“D”、“I”型记录，表示先删除后插入；当主键不变时，记录一对“U”、“U”型记录，分别表示新值和旧值；
-- 手工刷新MV
EXEC dbms_mview.refresh('mv_tbl1'); -- 刷新MV，对MV日志进行归并处理，对每一个主键，只执行其归并后结果的操作
SELECT * FROM mlog$_tbl1;
-- 检查trigger测试表
SELECT * FROM mv_tbl1_tri; -- 对修改后的新主键13的所有后续修改只触发了1条insert；
DELETE FROM tbl1; -- 生成3条“D”MV日志记录
COMMIT;
SELECT * FROM tbl1;
SELECT * FROM mlog$_tbl1;
-- 手工刷新MV
EXEC dbms_mview.refresh('mv_tbl1'); -- 刷新MV，触发3条delete，清空MV日志
SELECT * FROM mlog$_tbl1;
-- 检查trigger测试表
SELECT * FROM mv_tbl1_tri;
/***
结论
1. 在MV上可以建立触发器
2. MV触发器基于刷新时间点的MV日志归并结果，在一些场景（只要记录两次刷新时间点数据的差异，不需要记录两次刷新之间的历史变化）可以简化应用处理。
***/