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

落日映苍穹つ 2022-05-17 03:47 325阅读 0赞

仅以此文感谢[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 说:

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

大猫 说:

那我省了