创建快速可更新物化视图的查询语句，使用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 --
【MV】group by查询子句是否包含count(*)对物化视图刷新的影响
-
secooler
2011-06-13 22:09:03 -
Linux操作系统
- 原创