ITPub博客

首页 > 数据库 > Oracle > 快速刷新多表连接聚集物化视图

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

原创 Oracle 作者:lifewise 时间:2007-10-09 18:13:09 0 删除 编辑

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

仅以此文感谢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,太智能了。

大猫 说:

由于这些表更新不是很多,想要在commit的时候,作fast refresh on commit

大猫 说:

不知道因为这个group by 的存在能否实现?

大猫 说:

问题完毕。

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)
--------
--
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同学:)

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

请登录后发表评论 登录
全部评论

注册时间:2008-01-07

  • 博文量
    52
  • 访问量
    488720