oralce 使用物化视图实例

--创建物化视图 30分钟刷新一次
CREATE MATERIALIZED VIEW tblcheckitemview_mv
REFRESH COMPLETE
START WITH SYSTIMESTAMP
NEXT SYSTIMESTAMP + INTERVAL '30' MINUTE
AS
SELECT * FROM tblcheckitemview;

--启用物化视图自动刷新
BEGIN
DBMS_MVIEW.REFRESH('tblcheckitemview_mv', 'C', atomic_refresh=>false);
END;
/

--创建索引
CREATE INDEX inx_tblcheckitemview_mv_n1 ON tblcheckitemview_mv (moname);
CREATE INDEX inx_tblcheckitemview_mv_n2 ON tblcheckitemview_mv (mocode);

 

--授权给外部系统
grant select on tblcheckitemview_mv to ebs_io;