物化视图使用总结-CSDN博客

本文探讨了物化视图的刷新策略，包括oncommit和ondemand两种模式，以及快速（FAST）、完全（COMPLETE）和强制（FORCE）三种刷新方法。详细分析了不同刷新策略在实际应用中的优缺点，提供了具体实例和限制条件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

物化视图使用总结

物化视图的刷新有二类，分别是：on commit ；on demand。刷新方法有三种分别是：快速（FAST），完全（COMPLETE），强制（FORCE）；

ON COMMIT 与DEMAND 在应用中的问题

ON COMMIT

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

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解决。

表１：快速刷新

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

表２：完全刷新

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或手工。

FAST、FORCE、COMPLETE区别

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

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

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

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

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

1.

物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM；

2.

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

只包含连接的物化视图：

1.

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

2.

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

3.

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

4.

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

5.

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

6.

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

包含聚集的物化视图：

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(*)；

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物化视图；

物化视图包含MIN或MAX聚集函数；

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

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

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

9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；

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

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

12.对于包含了ROLLUP、CUBE、GROUPING 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-12043或Ora-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;

第三步：