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

快速刷新的物化视图创建比较麻烦，限制条件比较多，本文参考Oracle 11g 11.2版本官方文档，总结一般情况、含有联接、含有聚合计算、UNION ALL等情况下的限制条件。

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

定义物化视图的查询语句限制如下：

• 1.物化视图中不能含有非重复表达式的引用，例如SYSDATE和ROWNUM.
• 2.物化视图中不能出现RAW或LONG RAW数据类型的引用。
• 3.查询语句的SELECT部分语不能出现子查询。
• 4.SELECT子句部分不能包含分析性质的函数，例如RANK。
• 5.不能含有MODEL子句。
• 6.子查询中不能含有HAVING子句。
• 7.不能出现含有ANY,ALL或NOT EXISTS的嵌套子查询。
• 8.不能含有[START WITH …] CONNECT BY子句。
• 9.不能包含不同站点的多个明细表。
• 10.ON COMMIT刷新方式的物化视图不能有远程明细表的引用。
• 11.嵌套物化视图必须含有表联接或者聚合计算。
• 12.物化视图包含联接和GROUP BY子句的聚合计算时，不能select from 一个有索引的表。

只包含联接的物化视图快速刷新限制条件

定义物化视图的查询语句只含有联接且没有聚合计算，实现快速刷新除满足通用条件外，还要满足条件如下：

• 1.不能含有GROUP BY子句或聚合计算。
• 2.FROM中出现的每个基表的ROWID必须出现在SELECT子句中。
• 3.FROM中出现的每个基表都必须创建基于ROWID的物化视图日志(MATERIALIZED VIEW LOG)。
• 4.SELECT子句中不能含有object类型的列。

另外，以下情况将会影响快速刷新的效率：

• 1.定义的查询语句含有外联接，如果这样的话，请考虑重写查询语句改为内连接。
• 2.SELECT子句中含有多个表的列运算。

包含聚合的物化视图快速刷新限制

定义物化视图的查询语句含有聚合计算，实现快速刷新除满足通用条件外，还要满足条件如下：

• 1.所有基表必须建立物化视图日志，创建日志必须满足：
  • 1.1 包含在物化视图中引用的所有列，确保这些列没有被加密。
  • 1.2 需要指定ROWID和INCLUDING NEW VAUES。
  • 1.3 如果基表会有插入/直接导入，删除，更新数据的混合操作，需要指定SEQUENCE子句。
• 2.只有SUM,COUNT,AVG,STDDEV,VARIANCE,MIN和MAX才支持快速刷新。
• 3.必须有写COUNT(*)。
• 4.聚合函数必须出现在表达式的最外层，也就是说像AVG(AVG(X))或AVG(x)+AVG(x)都是不允许的。
• 5.对每一个聚集计算如AVG(expr),相应的COUNT(expr)也必须要有，Oracle建议SUM(expr)也写上。
• 6.如果含有VARIANCE(expr)或STDDEV(expr),COUNT(expr)和SUM(expr)都必须写，Oracle建议SUM(expr*expr)也写上。
• 7.查询中定义的SELECT列不能是来自多个表的列复杂运算，可以考虑使用嵌套物化视图作为变通方案。
• 8.GROUP BY中的列都要在SELECT里出现。
• 9.如果物化视图日志中过滤列字段类型为CHAR，那么基表所在的数据库和物化视图所在的数据库的字符集必须要一样。
• 10.如果物化视图有以下情况，增量刷新只支持DML操作中的INSERT和直接装载(direct loads)操作，这种物化视图叫只能插入(insert-only)的物化视图：
  • 10.1 物化视图有MIN或者MAX聚合计算。
  • 10.2 物化视图有SUM(expr)但是没有COUNT(expr)。
  • 10.3 物化视图没有指定COUNT(*)。
• 11.含有MAX或MIN聚合计算的物化视图如果没有WHERE子句，是支持基表增删改的增量刷新的。
• 12.物化视图的FROM子句中有普通视图或者子查询，如果普通视图可以被完全合并是可以支持增量刷新的。(PS.这一条不懂啥意思)
• 13.如果没有外联接，WHERE子句没有限制。
• 14.有外联接和聚合计算的物化视图是支持增量刷新的，只有外接表数据变更的时候。另外，唯一约束必须存在于表的内连接列上。如果有外联接，所有连接必须用AND和=操作符。
• 15.对于含有CUBE，ROLLUP，GOUPING SETS的物化视图，会有以下限制：
  • 15.1SELECT列表要包含GROUP BY所有字段的GROUPING_ID或GROUPING。举个例子，GROUP BY子句是GROUP BY CUBE(a, b)，那么SELECT列表中应该包含GROUPING_ID(a, b)或GROUPING(a) , GROUPING(b)，这样才会使物化视图增量刷新。
  • 15.2 GROUP BY不能导致多个groupings的情况，比如，GROUP BY a, ROLLUP(a, b)就不会增量刷新因为这会导致多个groupings，(a), (a, b), (a)。

包含UNION ALL的物化视图快速刷新限制

含有UNION ALL的物化视图REFRESH FAST需要满足以下条件：

• 1.UNION ALL操作必须在查询语句的顶层。UNION ALL不能被嵌套在一个子查询里面，这里有一个例外：SELECT * FROM后面直接跟含有UNION ALL的子查询。

例如：

CREATE VIEW view_with_unionall AS
(SELECT c.rowid crid, c.cust_id, 2 umarker
   FROM customers c WHERE c.cust_last_name = 'Smith'
  UNION ALL
 SELECT c.rowid crid, c.cust_id, 3 umarker
   FROM customers c WHERE c.cust_last_name = 'Jones');

 CREATE MATERIALIZED VIEW unionall_inside_view_mv
REFRESH FAST ON DEMAND AS
 SELECT * FROM view_with_unionall;

• 2.UNION ALL的每一部分查询必须满足聚合物化视图货连接物化视图的增量刷新条件。相应的物化视图日志也要建上。
• 3.UNION ALL的每一部分查询必须包含一列标记列，这个标识列必须是数值或者字符型的固定数值，而且这一列在每一部分的查询中SELECT列顺序相同。
• 4.一些特性如外联接，只能插入(insert-only)的物化视图查询和远程表都是不支持UNION ALL物化视图的。然而，那些被用于复制的不包含联接和聚集计算的物化视图，是可以增量刷新的。
• 5.创建包含UNION ALL的增量刷新物化视图，版本需满足9.2.0或更高才能满足兼容。

参考：
https://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007013

更多：
http://c-xuan.com/2017/07/13/20170713001