基于主键创建物化视图(MATERIALIZED VIEW)

    物化视图在同步数据方案中处于中低级的方案，但对有有些针对报表统计的分析系统，物化视图通过提前生成实际的物理数据，可以为报表统计提供更加快捷的查询效率，能够配合高效的快速刷新模式，它还是一种非常好的解决方案。

 

创建物化视图（把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.