Oracle认证专家视频教程-OCP全套教程之学习笔记-MVIEW
发布时间:2020-12-12 14:45:47 所属栏目:百科 来源:网络整理
导读:知识要点 mview基本概念 基本语法 手动刷新 fast+commit刷新 fast+start with next刷新 可更新物化视图 mview基本概念 materialized view 占用自己的存储空间,物化视图,也叫快照,默认行的内容只读 基本语法 创建物化视图日志 create materialized view lo
知识要点
mview基本概念materialized view 占用自己的存储空间,物化视图,也叫快照,默认行的内容只读 基本语法
create materialized view log on tabname;要有一个主键
create materialized view log on tabname with rowid;
create materialized view <> refresh <刷新方式 > on <刷新时间> as select .....
complete(完全刷新)
fast(依赖于物化视图日志,识别哪些行发生了更改)
force(强制刷新)
on demand 手动刷新
on commit;事务提交就刷新
start with / next 指定刷新时间
exec dbms_mview.refresh('mviewname','fast');
exec dbms_mview.refresh('mviewname','c');
手动刷新
sys>create user mvl identified by 123;
sys>grant connect,resource to mvl;
sys>grant select on scott.emp to mvl;
sys>grant create materialized view to mvl;
sys>grant execute on dbms_mview to mvl;
创建
mvl>create materialized view emp as select * from scott.emp;
scott用户做更新
scott>update emp set sal = sal -2;
scott>commit;
mvl>exec dbms_mview.refresh('emp','c');
mvl>select * from empf1;
fast+commit刷新
sys>grant create materialized view to scott;
scott>create materialized view log on emp;
sys>grant select on scott.MLOG$_EMP to mvl;
sys>grant on commit refresh on scott.emp to mvl;
mvl>create materialized view empf1 refresh fast on commit as select * from scott.emp;
scott>update emp set sal = sal + 2;
scott>commit;
mvl>select * from empf1;
fast+start with next刷新
mvl>create materialized view empf2 refresh fast start with sysdate next sysdate+1/1440 as select * from scott.emp;
scott>update emp set sal = sal + 2;
scott>commit;
mvl>select * from empf1;
可更新物化视图mvl>create materialized view empf3 refresh fast for update start with sysdate next sysdate+1/1440 as select * from scott.emp; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |