oracle11g - 存储来自物化视图的数据
问题描述
我有一个物化视图(MV),它将在每天 23:00 刷新。它将从一个大型事务表(例如 1 亿条记录)中选择并汇总用于报告目的的数据。
MV 非常简单,只包含 4 列和 7 条记录。每次用户生成报告时,它总是以 MV 中的方式显示数据。现在用户请求能够查看去年的数据。由于我的 MV 总是替换现有数据,我无法实现用户请求。
我的问题1.是否可以将MV中的数据自动存储在持久表中?2. 每次MV刷新完成时,是否可以创建触发器将MV中的数据插入到另一个表中?
解决方案
物化视图没有触发器。然而,可更新物化视图确实有触发器,但它有一个问题,它必须基于单个表。
基于不止一张表
CREATE MATERIALIZED VIEW LOG ON EMP;
CREATE MATERIALIZED VIEW mv_test
REFRESH FAST WITH PRIMARY KEY
FOR UPDATE
AS
SELECT *
FROM emp em JOIN DEPT de ON EM.DEPTNO = DE.DEPTNO;
ORA-12013: 可更新的物化视图必须足够简单才能进行快速刷新
基于单表
CREATE MATERIALIZED VIEW mv_test
REFRESH FAST WITH PRIMARY KEY
FOR UPDATE
AS
SELECT * FROM emp;
Materialized View created.
扳机
CREATE OR REPLACE TRIGGER test_tg
BEFORE INSERT OR UPDATE OF ENAME, MGR
ON MV_TEST
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 0;
NULL;
-- do as per the logic
EXCEPTION
WHEN OTHERS
THEN
NULL;
-- Consider logging the error and then re-raise
RAISE;
END test_tg;
Trigger created.
如果要求拥有历史数据,为什么不考虑使用可以使用调度程序作业执行的存储过程来实现标准事务表的数据持久性。
正如您所引用的查询具有 1 亿条记录的大表一样,我的估计是使用FOLL ALL
或BULK COLLECT
考虑批处理,不用说;这是一个不同的话题。
以下是过程和调度程序作业的伪代码,根据需要进行更改。使用 INSERT 或 MERGE
使用 INSERT 的过程
CREATE OR REPLACE PROCEDURE historical_records (p_emp_no emp.empno%TYPE)
IS
BEGIN
FOR rec IN ( SELECT ename, mgr, SUM (sal) tot_sal
FROM scott.emp
WHERE empno = p_emp_no
GROUP BY ename, mgr)
LOOP
INSERT INTO hist_table (empno,
ename,
mgr,
sal_tot)
VALUES (rec.empno,
rec.ename,
rec.mgr,
rec.tot_sal);
END LOOP;
END;
使用 MERGE 的过程
CREATE OR REPLACE PROCEDURE historical_records (p_emp_no emp.empno%TYPE)
IS
BEGIN
MERGE INTO hist_table trg
USING ( SELECT ename, mgr, SUM (sal) tot_sal
FROM scott.emp
WHERE empno = p_emp_no
GROUP BY ename, mgr) src
ON (trg.empno = src.empno)
WHEN MATCHED
THEN
UPDATE SET trg.ename = src.ename, trg.mgr = src.mgr
WHEN NOT MATCHED
THEN
INSERT (trg.empno, trg.ename, trg.sal_tot)
VALUES (src.empno, src.ename, src.tot_sal);
END;
调度程序作业
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'HIST_PROC_JOB',
job_type => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN historical_records; END;',
start_date => SYSDATE,
repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=05',
end_date => NULL,
enabled => TRUE,
comments => 'Historical data insertion');
END;
/
推荐阅读
- haskell - Haskell [(Int,Int)] -> [Int] 函数中的非穷举模式
- c - 将宏缩进到与普通源代码相同的宽度
- javascript - how to write a javascript for copying a substring using substring() and substr() methods
- javascript - 输入数字后如何激活按钮
- libreoffice - 如何垂直对齐段落中心
- c# - 带有子视图的 MvvmCross Wpf 视图
- javascript - 如何使用敲除创建选择
- python - 我想得到列表的总和,除了迭代值
- r - ggpairs错误:cor.test.default(x,y,方法=方法,使用=使用)中的错误:没有足够的有限观察
- python - 我如何使用源和目标 MAC 地址嗅探以太网