知识要点
- mview基本概念
- 基本语法
- 手动刷新
- fast+commit刷新
- fast+start with next刷新
- 可更新物化视图
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');
手动刷新
- 创建实验用户mvl
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刷新
- fast刷新需要首先创建物化视图日志
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;
- 1分钟之后可以看到已经同步
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;
