--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;