创建定期增量刷新物化视图
1,在源库建立被同步表的物化视图日志:
create materialized view log on db_sbzs.t_sb_yzpz tablespace m_view;
2,在目标库建立物化视图实现定期增量刷新:
create materialized view db_jydjz.t_sb_yzpz
refresh fast
on demand
start with sysdate
next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 00:00:00'),'dd-mm-yyyy hh24:mi:ss')
with primary key
as
select * from db_sbzs.t_sb_yzpz@db_link_jysjfz
where gljg_dm like '2445202%' and LR_SJ > date'2011-10-1';
select owner,segment_name,bytes/1024/1024 from dba_segments where tablespace_name='M_VIEW';
查看视图被刷新的最后时间查看相应视图:dba_mview_analysis
查看物化视图日志视图:MLOG$_tablename (tablename为实际表)
用系统包来刷新物化视图的方法:
begin
dbms_mview.refresh(TAB=>'an_user_base_file_no_charge',
METHOD=>'FAST',
PARALLELISM=>1);
end;
物化视图优点:优化对视图查询速度!(可以建索引),同步数据
缺点:占存储空间