【MV】创建可以去除表中重复记录的物化视图

有关物化视图的基本信息可以参考Oracle官方文档“Data Warehousing Guide”（http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/toc.htm）
本文以创建满足“可以去除表中重复值”需求的物化视图为例感受一下物化视图带给我们的功能。

1.初始化实验环境
secooler@ora10g> drop materialized view log on t;
secooler@ora10g> drop table t purge;
secooler@ora10g> create table t (x int);
secooler@ora10g> insert into t values (1);
secooler@ora10g> insert into t values (2);
secooler@ora10g> insert into t values (3);
secooler@ora10g> insert into t values (4);
secooler@ora10g> insert into t values (6);
secooler@ora10g> insert into t values (6);
secooler@ora10g> commit;

2.创建物化视图日志
从原理上讲，物化视图的刷新是通过物化视图日志来完成的。
secooler@ora10g> create materialized view log on t with sequence, rowid (x) including new values;

Materialized view log created.

3.创建随commit刷新的物化视图
secooler@ora10g> drop materialized view mv_t;
secooler@ora10g> create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select * from t group by x;

Materialized view created.

4.测试物化视图效果
1）t表包含的数据
secooler@ora10g> select * from t order by 1;

         X
----------
         1
         2
         3
         4
         6
         6

6 rows selected.

2）查看物化视图中的数据
secooler@ora10g> select * from mv_t order by 1;

         X
----------
         1
         2
         3
         4
         6

可见，此时该物化视图已经达到了去除t表中重复记录的目的。

3）验证物化视图随commit而刷新
secooler@ora10g> insert into t values (100);

1 row created.

secooler@ora10g> select * from t order by 1;

         X
----------
         1
         2
         3
         4
         6
         6
       100

7 rows selected.

secooler@ora10g> select * from mv_t order by 1;

         X
----------
         1
         2
         3
         4
         6

secooler@ora10g> commit;

Commit complete.

secooler@ora10g> select * from mv_t order by 1;

         X
----------
         1
         2
         3
         4
         6
       100

6 rows selected.

效果上可见物化视图是随commit进行刷新的。

5.创建手工刷新的物化视图
如果创建的是不带on commit选项，物化视图不会根据commit进行刷新，需要手工完成刷新。
1）恢复实验环境
secooler@ora10g> delete from t where x=100;

1 row deleted.

secooler@ora10g> commit;

Commit complete.

secooler@ora10g> drop materialized view mv_t;

Materialized view dropped.

2）创建手工刷新的物化视图
secooler@ora10g> create materialized view mv_t build immediate refresh fast enable query rewrite as select * from t group by x;

Materialized view created.

secooler@ora10g> insert into t values (100);

1 row created.

secooler@ora10g> commit;

Commit complete.

secooler@ora10g> select * from t order by 1;

         X
----------
         1
         2
         3
         4
         6
         6
       100

7 rows selected.

secooler@ora10g> select * from mv_t order by 1;

         X
----------
         1
         2
         3
         4
         6

效果已显然，在commit之后，物化视图并未进行刷新。

3）使用手工刷新方法对物化视图进行刷新
secooler@ora10g> exec dbms_mview.refresh('mv_t', nested => true);

PL/SQL procedure successfully completed.

secooler@ora10g> select * from mv_t order by 1;

         X
----------
         1
         2
         3
         4
         6
       100

6 rows selected.

刷新成功。t表中新增加的记录“100”已经在物化视图中得以体现。

6.小结
本文通过一个具体需求演示了物化视图的使用。物化视图本身是一项空间换取时间的技术。在对数据统计效率要求较高的特定场景下，可以适当的引入物化视图技术。

Good luck.

secooler
10.12.01

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-680752/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-680752/