物化视图基于rowID快速刷新

最新推荐文章于 2024-07-24 06:39:49 发布

转载最新推荐文章于 2024-07-24 06:39:49 发布 · 155 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/523823-wu/p/8710261.html

本文介绍了一个关于创建物化视图的过程，包括创建物化视图日志、物化视图本身及其索引，并提供了存储过程及定时刷新机制的具体实现。

--1创建log
create MATERIALIZED VIEW LOG ON gh_qyghgx WITH rowid;
create MATERIALIZED VIEW LOG ON gh_qyxx WITH rowid;
--2创建物化视图
create materialized view GH_QYGHGX_QYXX_UNION
REFRESH FAST ON DEMAND
AS
SELECT a.rowid as ar,gx.rowid gxid, A.QYBM,
A.TYSHXYDM,
A.nsrsbh,
A.zzjgdm,
A.QYMC,
A.QYZT,
A.SJJYDZ,
A.Gsrs,
GX.ghmc1 ,
GX.ghmc2 ,
GX.ghmc3 ,
GX.ghmc4 ,
GX.ghmc5,
GX.ghmc6 ,
GX.GHDM1,
GX.GHDM2,
GX.GHDM3,
GX.GHDM4,
GX.GHDM5,
GX.GHDM6,
GX.JHZT
from GH_QYGHGX GX
,GH_QYXX A
where A.QYBM = GX.QYBM;
--3创建索引
CREATE INDEX IN_GH_QYGHGX_QYXX_un_GHDM1 ON GH_QYGHGX_QYXX_UNION (GHDM1);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_GHDM2 ON GH_QYGHGX_QYXX_UNION (GHDM2);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_GHDM3 ON GH_QYGHGX_QYXX_UNION (GHDM3);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_GHDM4 ON GH_QYGHGX_QYXX_UNION (GHDM4);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_GHDM5 ON GH_QYGHGX_QYXX_UNION (GHDM5);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_GHDM6 ON GH_QYGHGX_QYXX_UNION (GHDM6);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_GSRS ON GH_QYGHGX_QYXX_UNION (GSRS);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_JHZT ON GH_QYGHGX_QYXX_UNION (JHZT);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_NSRSBH ON GH_QYGHGX_QYXX_UNION (NSRSBH);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_QYMC ON GH_QYGHGX_QYXX_UNION (QYMC);
CREATE INDEX IN_GH_QYGHGX_QYXX_un_TYSHXYDM ON GH_QYGHGX_QYXX_UNION (TYSHXYDM);

--4.创建存储过程（刷新需要大写）
create or replace procedure GH_QYGHGX_QYXX_UNION_PRO as
declare
begin
dbms_mview.refresh('GH_QYGHGX_QYXX_UNION');
end;
--5.设置定时刷新
declare
variable job1 number;
begin
dbms_job.submit(:job1,'GH_QYTCHF_LS_PRO;GH_QYGHGX_QYXX_UNION_PRO;',sysdate,'sysdate+5/1440');
end;

转载于:https://www.cnblogs.com/523823-wu/p/8710261.html