物化视图在同步数据方案中处于中低级的方案,但对有有些针对报表统计的分析系统,物化视图通过提前生成实际的物理数据,可以为报表统计提供更加快捷的查询效率,能够配合高效的快速刷新模式,它还是一种非常好的解决方案。
创建物化视图(把test用户下的T1同步到test1用户下的MV_T1)
1:创建源表
SQL> create table t1(id number primary key);
Table created.
2:创建物化日志(test用户下的T1源表)
CREATE MATERIALIZED VIEW LOG ON T1
WITH PRIMARY KEY
INCLUDING NEW VALUES;
3:创建物化视图(test1用户下的MV_T1物化视图)
CREATE MATERIALIZED VIEW MV_T1
BUILD IMMEDIATE
REFRESH FAST WITH PRIMARY KEY ------根据表主键增量refresh
ON DEMAND
ENABLE QUERY REWRITE ------物化视图允许可读写
AS
SELECT * FROM TEST.T1; ------查询的sql语句
4:插入数据测试
SQL> insert into t1 values(1);
1 row created.
SQL> commit;
5:增量刷新并测试同步
SQL> BEGIN
2 DBMS_MVIEW.REFRESH('MV_T1','f');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM MV_T1;
ID
----------
1
Note:如果需要同时刷新多个物化视图,必须用逗号把各个物化视图名称连接起来,并对每个视图都要指明刷新方式:
f:增量刷新,c:完全刷新,?:强制刷新
Eg:多个物化视图同时刷新
DBMS_MVIEW.REFRESH('MV_T1,MV_T2,MV_T3','fcf');
6:测试如果物化视图数据清空,能否使用增量的刷新的方式?(类似迁移历史数据)
6.1:插入数据
SQL> insert into t1 values(2);
1 row created.
SQL> insert into t1 values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID
----------
1
2
3
6.2:增量刷新
SQL> BEGIN
2 DBMS_MVIEW.REFRESH('MV_T1','f');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-32320: REFRESH FAST of "SYS"."MV_T1" unsupported after container table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 2
6.3:强制刷新/完全刷新
SQL> BEGIN
2 DBMS_MVIEW.REFRESH('MV_T1','?');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM MV_T1;
ID
----------
1
2
3
SQL> truncate table MV_T1;
Table truncated.
SQL>
SQL> BEGIN
2 DBMS_MVIEW.REFRESH('MV_T1','C');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> sELECT * FROM MV_T1;
ID
----------
1
2
3
Note:不支持这种清空历史数据,增量刷新的方式。
7:删除物化视图
SQL> drop MATERIALIZED VIEW LOG ON T1;
Materialized view log dropped.
SQL> drop MATERIALIZED VIEW MV_T1;
Materialized view dropped.