--以dba身份登录,为用户授予创建实体化视图的权限
--Grant Create Materialized View to 用户名;
--删除实体化视图
drop materialized view mv_zhaozhenlong;
--创建实体化视图
create materialized view mv_zhaozhenlong
refresh force on demand
enable query rewrite
as
select dept_id, item,sum(qty) as qty
from tb_zhaozhenlong
where rpt_date between to_date('2007-1-1','yyyy-mm-dd') and to_date('2007-1-5','yyyy-mm-dd')
group by dept_id,item;
--说明:
--实体化视图比普通视图的比较查询速度快;
--不受永久表数据改变的影响;
--如果要同步正式表的数据,需要手工刷新;
EXEC DBMS_MVIEW.REFRESH('mv_zhaozhenlong','CF');
--每个表可以创建一个实体化视图日志
create materialized view log on tb_zhaozhenlong
with rowid including new values;
--创建自动刷新的实体化视图
create materialized view mv_zhaozhenlong_2
build immediate
refresh complete --commit速度比较慢
on commit
as
select * from tb_zhaozhenlong;