oracle创建物化视图

1、创建DB_LINK
create public database link irtp_to_itunesdg3 connect to ICARDP identified by passwd using ‘itunesdg3’;

2、创建物化视图之前要创建相应的物化视图日志

SELECT constraint_name, table_name,CONSTRAINT_TYPE FROM all_constraints WHERE table_name = upper(‘lps_mst’); 查询是否有主键
SELECT constraint_name, table_name,CONSTRAINT_TYPE FROM all_constraints WHERE table_name = upper(‘hist_trans_m’);

create materialized view log on lps_mst;
create materialized view log on hist_trans_m with rowid;

3、创建fast视图的时候要根据主键或者 rowid 创建

create materialized view lps_mst
refresh fast on demand as select * from lps_mst@irtp_to_itunesdg3;

create materialized view hist_trans_m
refresh fast with rowid on demand as select * from hist_trans_m@irtp_to_itunesdg3;

4、执行刷新 刷新 F 代表 fast
exec dbms_mview.refresh (‘lps_mst’,‘F’);
exec dbms_mview.refresh (‘hist_trans_m’,‘F’);