oracle 物化视图多表合并,快速刷新多表连接聚集物化视图

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

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

selectcount(*)frommosm,attributesa,mo_attributesma,classesclwherem.mo_class_id=cl.mo_class_idandm.mo_id=ma.mo_idandma.attribute_id=a.attribute_idandcl.class_name= ?;

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

createMaterializedviewXXXXXXenablequeryrewriteasselectcl.class_name,count(*)frommosm,attributesa,mo_attributesma,classesclwherem.mo_class_id=cl.mo_class_idandm.mo_id=ma.mo_idandma.attribute_id=a.attribute_idgroupbycl.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而指定的

CREATEMATERIALIZEDVIEWLOGonmosWITHrowid,SEQUENCE(mo_id,mo_class_id)INCLUDINGNEWVALUES;CREATEMATERIALIZEDVIEWLOGonattributesWITHrowid,SEQUENCE(attribute_id)INCLUDINGNEWVALUES;CREATEMATERIALIZEDVIEWLOGonCLASSESWITHrowid,SEQUENCE(mo_class_id,class_name)INCLUDINGNEWVALUES;CREATEMATERIALIZEDVIEWLOGonmo_attributesWITHrowid,SEQUENCE(attribute_id)INCLUDINGNEWVALUE;createMaterializedviewbinzhangREFRESHFASTONCOMMITenablequeryrewriteasselectcl.class_name,count(*)frommosm,attributesa,mo_attributesma,classesclwherem.mo_class_id=cl.mo_class_idandm.mo_id=ma.mo_idandma.attribute_id=a.attribute_idgroupbycl.class_name;

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

SQL>showparametersquery_rewriteNAMETYPEVALUE------------------------------------ ----------- ------------------------------query_rewrite_enabledstringTRUEquery_rewrite_integritystringenforcedselect/*+ all_rows */count(*)frommosm,attributesa,mo_attributesma,classesclwherem.mo_class_id=cl.mo_class_idandm.mo_id=ma.mo_idandma.attribute_id=a.attribute_idandcl.class_name='XXXX';COUNT(1)----------

233942ExecutionPlan----------------------------------------------------------

0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=2 Card=1 Bytes=35)10SORT(AGGREGATE)21TABLEACCESS(FULL)OF'BINZHANG'(Cost=2Card=20Bytes=700)

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

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

/*+先测试删除delete*/SQL>select*frombinzhang;

..........................41rowsselected.SQL>deletefromclasseswhererownum=1;1rowdeleted.SQL>commit;Commitcomplete.SQL>selectcount(*)frombinzhang;COUNT(*)----------

40/*+delete work!*//*+再测试更新update*/SQL>select*frombinzhang;CLASS_NAMECOUNT(*)---------------------------------------- ----------ODB::Action54SQL>updateclassessetCLASS_NAME='xxxxxxxxxx'WHERECLASS_NAME='ODB::Action';1rowupdated.SQL>COMMIT;Commitcomplete.SQL>select*frombinzhang;CLASS_NAMECOUNT(*)---------------------------------------- ----------xxxxxxxxxx54/*+update work!*/

再次感谢YangTingKun同学:)