ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 物化视图使用总结

物化视图使用总结

原创 Linux操作系统 作者:buliaoqingtian 时间:2019-06-15 13:42:07 0 删除 编辑

前几天写了一篇关于物化视图的文章,感觉有些东西没写出来,后来自己有整理了一下本人工作中遇到的问题及如何解决的小例子,希望对看到这篇文章的人有一些帮助。


物化视图使用总结

物化视图的刷新有二类,分别是:on commit on demand。刷新方法有三种分别是:快速(FAST),完全(COMPLETE),强制(FORCE);

ON COMMIT DEMAND 在应用中的问题

ON COMMIT

如果选择on commit ,则在对主表应用上会造成速度,这是因为ORACLE在对主表操作提交后马上会进行刷新物化视图操作,这部分时间是也包括在提交时间中。

a) refresh force on commit:中对删,新增记录,物理视图都能真实反映主表的变化。同时这种情况下不用建物化视图日志表。缺点是提交时间长。

b) refresh fast on commit:中对新增能真实反映主表的变化,但对删除,修改则不能反映,必须进行一次完全刷新。

ON DEMAND

DEMAND必须用DBMS_MVIEW.REFRESH存储过程建立的JOB去定时刷新物化视图。

a) refresh fast on DEMAND:必须通过调用DBMS_MVIEW.REFRESH存储过程来进行快速刷新反映主表新增情况;但当对主表中的数据删除或修改时,快速刷新则会报错,因此必须调DBMS_MVIEW.REFRESH的完全刷新才能反映。可以通过建立JOB解决。

表1:快速刷新

declare
v_mvname varchar2(50);
begin
v_mvname:='MOCHA_FE_DOC_CONTENT_MV';
dbms_mview.refresh(v_mvname,'f');
end;
/

表2:完全刷新

declare
v_mvname varchar2(50);
begin
v_mvname:='MOCHA_FE_DOC_CONTENT_MV';
dbms_mview.refresh(v_mvname,'C');
end;
/

注意:用FAST 刷新物化视图,前提要新建物化视图日志表。

b) refresh force/complate on DEMAND:

在这种方式下物化视图也是无法自动刷新,必须通过JOB或手工。

FASTFORCECOMPLETE区别

FAST:增量式刷新,使用此方法必须有前提,就是建立物化视图日志表。

FORCE::如果可以以fast 方式刷新则用,否则完全刷新。

COMPLETE:先将物化视图表内容删除,然后再刷新。此方式缺少就是在刷新时间内用法在页面无法查到的所需要内容。

物化视图快速刷新限制条件

所有类型的快速刷新物化视图都必须满足的条件:

1.

物化视图不能包含对不重复表达式的引用,如SYSDATEROWNUM

2.

物化视图不能包含对LONGLONG RAW数据类型的引用。

只包含连接的物化视图:

1.

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

2.

不能包括GROUP BY语句或聚集操作;

3.

如果在WHERE语句中包含外连接,那么唯一约束必须存在于连接中内表的连接列上;

4.

如果不包含外连接,那么WHERE语句没有限制,如果包含外连接,那么WHERE语句中只能使用AND连接,并且只能使用“=”操作。

5.

FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。

6.

FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。

包含聚集的物化视图:

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

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

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

(2)必须指明ROWIDINCLUDING NEW VALUES

(3)如果对基本的操作同时包括INSERTUPDATEDELETE操作(即不是只包含INSERT操作),那么物化视图日志应该包括SEQUENCE

3.允许的聚集函数包括:SUMCOUNTAVGSTDDEVVARIANCEMINMAX

4.必须指定COUNT(*)

5.如果指明了除COUNT之外的聚集函数,则COUNT(expr)也必须存在;

比如:包含SUM(a),则必须同时包含COUNT(a)

6.如果指明了VARIANCE(expr)STDDEV(expr),除了COUNT(expr)外,SUM(expr)也必须指明;

Oracle推荐同时包括SUM(expr*expr)

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

8.当物化视图属于下面的某种情况,则快速刷新只支持常规DML插入和直接装载,这种类型的物化视图又称为INSERT-ONLY物化视图;

物化视图包含MINMAX聚集函数;

物化视图包含SUM(expr),但是没有包括COUNT(expr)

物化视图没有包含COUNT(*)

注意:如果建立了这种物化视图且刷新机制是ON COMMIT的,则会存在潜在的问题。当出现了UPDATEDELETE语句,除非手工完全刷新解决这个问题,否则物化视图至此以后都不再自动刷新,且不会报任何错误。

9.如果包含inline viewsouter joinsself joinsgrouping set,则兼容性的设置必须在9.0以上;

10.如果物化视图建立在视图或子查询上,则要求视图必须可以完全合并的。

11.如果没有外连接,则对WHERE语句没有限制。如果包含外连接,则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图,快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。

12.对于包含了ROLLUPCUBEGROUPING SET的物化视图必须满足下列限制条件:

SELECT语句列表中应该包含GROUPING标识符:可以是GROUP BY表达式中所有列的GROUPING_ID函数,也可以是GROUP BY表达式中每一列的GROUPING函数;

例如:GROUP BY语句为:GROUP BY CUBE(a, b),则SELECT列表应该包括GROUPING_ID(a, b)或者GROUPING(a)GROUPING(b)

GROUP BY不能产生重复的GROUPING

比如:GROUP BY a, ROLLUP(a, b)则不支持快速刷新,因为包含了重复的GROUPING(a), (a, b), (a)

包含UNION ALL的物化视图:

1.UNION ALL操作必须在查询的顶层。可以有一种情况例外:UNION ALL在第二层,而第一层的查询语句为SELECT * FROM

2.UNION ALL操作连接在一起的每个查询块都应该满足快速刷新的限制条件;

3.SELECT列表中必须包含一列维护列,叫做UNION ALL标识符,每个UNION ALL分支的标识符列应包含不同的常量值;

4.不支持外连接、远端数据库表和包括只允许插入的聚集物化视图定义查询;

5.不支持基于分区改变跟踪(PCT)的刷新;

6.兼容性设置应设置为9.2.0。

嵌套物化视图:

嵌套物化视图的每层都必须满足快速刷新的限制条件;

对于同时包含聚集和连接的嵌套物化视图,不支持ON COMMIT的快速刷新。

查询重写

create materialized view 语句中:enable query rewrite 选项表示:查询重写。

要想让查询重写有效,必须要先设置参数query_rewrite_enable值为true

个人工作中的一点总结

当你对主表进行更新或删除操作后,对物化视图进行快速刷新,则会报ORA-12043Ora-32041错误(说对于update/delete 快速刷新不支持)

如果出现这种情况,则可以通过DBMS_MVIEW.EXPLAIN_MVIEW()对物化视图进行分析,查看为什么不能用FAST对物化视图进行快速刷新。在用这个系统包之前,必须先用ORACLE提供的一个脚本去建表MV_CAPABILITIES_TABLE,这个脚本的位置位于:oracle_homerdbmsadmin目录中,名称是:utlxmv.sql。下面兴举一小例(物化视图名称:mv_test

第一步:

SQL>start f:oracle|ora92rdbmsadmin utlxmv.sql

建表成功

第二步:

SQL>DECLARE

V_name varchar2(40);

Begin

V_name:=’mv_test’;

Dbms_mview.explain_mview(v_name);

Commit;

End;

第三步:


SELECT CAPABILITY_NAME, RELATED_TEXT, MSGTXT FROM MV_CAPABILITIES_TABLE
WHERE POSSIBLE
= 'N' AND CAPABILITY_NAME NOT LIKE '%PCT%';

第四步:

根据第三步的结果对物化视图语句或视图日志进行修改。

如果你的物化视图的select语句中有SUM()COUNT()函数及group by ,并且对于主表有INSERTUPDATEDELETE操作时;对于快速刷新功能,版本为9.2.0.1不支持快速刷新。而版本是9.2.0.4就支持快速刷新。

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

上一篇: 年终回忆
请登录后发表评论 登录
全部评论

注册时间:2005-08-03

  • 博文量
    88
  • 访问量
    66481