仅以此文感谢YangTingKun同学:)

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

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

[@more@]

仅以此文感谢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_idand 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_idand 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 TRUEquery_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)
----------
233942Execution 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同学:)

ITPub博客

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

最新文章