玉面飞龙的BLOG

快速刷新多表连接聚集物化视图

June 22nd, 2007 | Categories: Boring | Tags:

仅以此文感谢YangTingKun同学:)

有物化视图的问题都找他。

起因是夺表连接的聚集SQL无法继续优化，反应时间几秒左右，buffer gets&cpu成本等还很高；加上还需要频繁执行。好在是，相关的表不是经常更新，只是查询频繁。正好适合物化视图。

select count(*)
from mos m, attributes a, mo_attributes ma, classes cl
where m.mo_class_id = cl.mo_class_id
and m.mo_id = ma.mo_id
and ma.attribute_id = a.attribute_id
and cl.class_name = ?;

准备使用如下SQL创建物化视图，

create Materialized view XXXXXX
enable query rewrite
as
select cl.class_name,count(*)
from mos m, attributes a,
mo_attributes ma, classes cl
where m.mo_class_id = cl.mo_class_id
and m.mo_id = ma.mo_id
and ma.attribute_id = a.attribute_id
group by cl.class_name;

则关键的实现就在于如何刷新了；咨询了yangtingkun，Oracle竟然能够对这种，多表连接，还又聚集的表作on commit fast refresh,太智能了。

大猫说:

由于这些表更新不是很多，想要在ｃｏｍｍｉｔ的时候，作ｆａｓｔ　ｒｅｆｒｅｓｈ　ｏｎ　ｃｏｍｍｉｔ

大猫说:

不知道因为这个ｇｒｏｕｐ　ｂｙ　的存在能否实现？

大猫说:

问题完毕。

yangtingkun 说:

包含聚集的物化视图：

1.必须满足所有快速刷新物化视图都满足的条件；

2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：

(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；

(2)必须指明ROWID和INCLUDING NEW VALUES；

(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。

3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；

4.必须指定COUNT ；

7.SELECT列表中必须包括所有的GROUP BY列；

yangtingkun 说:

你这个快速刷新应该不成问题，只要正确的设置物化视图日值，并正确的建立物化视图就可以了。

详细步骤如下,

创建MATERIALIZED VIEW LOG 和MATERIALIZED VIEW，需要制定一些关键字; rowid ,SEQUENCE都是为了能够on commit fast refresh而指定的

CREATE MATERIALIZED VIEW LOG on mos WITH rowid ,SEQUENCE (mo_id,mo_class_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG on attributes WITH rowid,SEQUENCE (attribute_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG on CLASSES WITH rowid ,SEQUENCE (mo_class_id,class_name) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG on mo_attributes WITH rowid ,SEQUENCE (attribute_id) INCLUDING NEW VALUE;

create Materialized view binzhang
REFRESH FAST ON COMMIT
enable query rewrite
as
select cl.class_name,count(*)
from mos m, attributes a,
mo_attributes ma, classes cl
where m.mo_class_id = cl.mo_class_id
and m.mo_id = ma.mo_id
and ma.attribute_id = a.attribute_id
group by cl.class_name;

然后看query rewrite能否使用到这个物化视图，

SQL> show parameters query_rewrite

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

select /*+ all_rows */count(*)
from mos m, attributes a, mo_attributes ma, classes cl
where m.mo_class_id = cl.mo_class_id
and m.mo_id = ma.mo_id
and ma.attribute_id = a.attribute_id
and cl.class_name = 'XXXX';
COUNT(1)
----------
   233942
Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=2 Card=1 Bytes=35)
   1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'BINZHANG' (Cost=2 Card=20 Bytes=700)

CBO模式下，query_rewrite_enabled=TRUE,发生了sql rewrite，自动选择了刚创建的物化视图，性能和反应时间大幅度提高。

再测试能否在发生了DML的时候做fast refresh on commit

/*+先测试删除delete*/

SQL> select * from binzhang;
..........................
41 rows selected.
SQL> delete from classes where rownum=1;
1 row deleted.

SQL> commit;
Commit complete.

SQL> select count(*) from binzhang;
COUNT(*)
----------
40
/*+delete work!*/
/*+再测试更新update*/
SQL> select * from binzhang;
CLASS_NAME COUNT(*)
---------------------------------------- ----------
ODB::Action 54

SQL> update classes set CLASS_NAME='xxxxxxxxxx' WHERE CLASS_NAME='ODB::Action';
1 row updated.

SQL> COMMIT;
Commit complete.

SQL> select * from binzhang;
CLASS_NAME COUNT(*)
---------------------------------------- ----------
xxxxxxxxxx 54
/*+update work!*/

再次感谢YangTingKun同学:)

大猫说:

你到时候来上海参加ｏｒａｃｌｅ　ｗｏｒｌｄ吗？

大猫说:

来的话我请客吃皮萨饼

yangtingkun 说:

估计去不了，那个时候正好比较忙

大猫说:

那我省了

大猫说:

ｏｒａｃｌｅ的物化视图振强，连多表连接的ｆａｓｔ　ｒｅｆｒｅｓｈ都可以做

yangtingkun 说: