ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【MV】group by查询子句是否包含count(*)对物化视图刷新的影响

【MV】group by查询子句是否包含count(*)对物化视图刷新的影响

原创 Linux操作系统 作者:secooler 时间:2011-06-13 22:09:03 0 删除 编辑
  创建快速可更新物化视图的查询语句,使用group by进行分类查询时,是否使用count(*)将对物化视图刷新产生影响。本文给出测试过程,并进行总结。

1.初始化环境
1)准备物化视图基表
create table t (x int, y int,z int);
insert into t values (1,1,1);
insert into t values (2,2,2);
insert into t values (3,3,3);
insert into t values (4,4,4);
insert into t values (5,5,5);
insert into t values (6,6,6);
insert into t values (6,6,6);
commit;
sec@ora10g> select * from t;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         6          6          6

7 rows selected.

2)创建物化视图日志
注:包含所有字段
sec@ora10g> create materialized view log on t with sequence, rowid (x,y,z) including new values;

Materialized view log created.

3)创建物化视图
sec@ora10g> create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z from t group by x,y,z;

Materialized view created.

sec@ora10g> select * from mv_t order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6

6 rows selected.

注意:此处创建的物化视图中未包含“count(*)”!

2.测试物化视图使用效果
1)验证创建结果
sec@ora10g> select * from t order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         6          6          6

7 rows selected.

sec@ora10g> select * from mv_t order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6

6 rows selected.

2)验证物化视图是否随记录增加而更新
sec@ora10g> insert into t values (7,7,7);

1 row created.

sec@ora10g> select * from t    order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         6          6          6
         7          7          7

8 rows selected.

sec@ora10g> select * from mv_t order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6

6 rows selected.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from t    order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         6          6          6
         7          7          7

8 rows selected.

sec@ora10g> select * from mv_t order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         7          7          7

7 rows selected.

结论一:针对插入操作,数据变化可以随commit刷新到物化视图中,不过注意这是有前提的,前提是插入操作之前没有删除和更新操作。

3)验证物化视图是否随记录删除而更新
sec@ora10g> delete from t where x=1;

1 row deleted.

sec@ora10g> select * from t    order by x;

         X          Y          Z
---------- ---------- ----------
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         6          6          6
         7          7          7

7 rows selected.

sec@ora10g> select * from mv_t order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         7          7          7

7 rows selected.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from t    order by x;

         X          Y          Z
---------- ---------- ----------
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         6          6          6
         7          7          7

7 rows selected.

sec@ora10g> select * from mv_t order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         7          7          7

7 rows selected.

结论二:针对删除操作,数据变化无法随commit刷新到物化视图。

4)验证物化视图是否随记录修改而更新
sec@ora10g> update t set x=8,y=8,z=8 where x=7;

1 row updated.

sec@ora10g> select * from t    order by x;

         X          Y          Z
---------- ---------- ----------
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         6          6          6
         8          8          8

7 rows selected.

sec@ora10g> select * from mv_t order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         7          7          7

7 rows selected.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from t    order by x;

         X          Y          Z
---------- ---------- ----------
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         6          6          6
         8          8          8

7 rows selected.

sec@ora10g> select * from mv_t order by x;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         7          7          7

7 rows selected.

结论三:针跟新除操作,数据变化无法随commit刷新到物化视图。

3.可以手动完全刷新解决此问题
sec@ora10g> exec dbms_mview.refresh('mv_t', 'c');

PL/SQL procedure successfully completed.

sec@ora10g> select * from mv_t order by x;

         X          Y          Z
---------- ---------- ----------
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         8          8          8

6 rows selected.

此时物化视图内容已经根据基表内容刷新完毕,结果正确。

4.完美解决方案
保证随基表的变化刷新物化视图的根本解决方法是,在创建物化视图的查询语句中使用count(*)。
create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;

具体测试过程这里不赘述。

5.小结
  根据这里测试结果提醒我们需要注意的内容是,了解物化视图不同创建方法对刷新结果的影响。防止不必要的问题发生。

Good luck.

secooler
11.06.13

-- The End --

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

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8197326