以下内容只作个人笔记 !
物化视图(定时刷新)
1:在源库上创建物化视图日志
create materialized view log on 源库表 with primary key(注意:源库表一定要有主键)
2:在中间库上创建物化视图
CREATE MATERIALIZED VIEW MV_WHST_SJ
REFRESH FAST ON DEMAND
AS
SELECT * FROM 源库表@DBLK_CZRK
3:在中间库上创建日志表
create table RZ_WUST
(
PK_NAME NUMBER(22), --主键字段
REFRESH_DATE DATE,
REFRESH_TYPE NUMBER
);
4:给中间库上日志导入数据
insert into RZ_WHST(pk_name, refresh_date, refresh_type)
(select pk_name, sysdate, 0 from MV_WHST_SJ);
5:给中间库上的日志表创建索引
--日志表加索引
create index IDX_RZ_WHST_PK on RZ_WHST(PK_NAME);
-- PK_NAME在日志表中不是主键,是一个外键,可能会重复,因此只要创建索引
create index IDX_RZ_WHST_DATE on RZ_WHST(REFRESH_DATE);
6:给物化视图创建触发器
create or replace trigger trigger_MV_WHST_SJ
after insert or update or delete on MV_WHST_SJ
for each row
declare
begin
if inserting then
insert into RZ_WHST
(pk_name, refresh_date, refresh_type)
values
(:new.编号, sysdate, 0);
elsif updating then
update RZ_WHST
set refresh_date = sysdate,
refresh_type = 1
where pk_name = :new.编号;
elsif deleting then
update RZ_WHST
set refresh_date = sysdate,
refresh_type = 2
where pk_name = :new.编号;
end if;
end trigger_MV_WHST_SJ;
7:创建刷新物化视图的存储过程
create or replace procedure P_HZ_SJ_CZRK_REFRESH as
begin
dbms_mview.refresh('MV_WHST_SJ'); --物化视图名字
end;
8: 创建JOB定时刷新物化视图(同步数据)
variable job number;
begin
sys.dbms_job.submit(job => :job,
what => 'P_HZ_SJ_CZRK_REFRESH;',
next_date => to_date('24-12-2009 10:12:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'trunc(sysdate+1/24/6/10,''mi'')');
--刷新频率1分钟太频繁,建议改为每天或每隔数小时。
commit;
end;
9: 创建外部接口视图
create view V_HZ_SJ_CZRK as
select a.*, b.refresh_date, b.refresh_type
from MV_WHST_SJ a, RZ_WHST b
where a.编号 = b.pk_name;