博客

物化视图的刷新方式说明

  • Post author:
  • Post category:其他



2019独角兽企业重金招聘Python工程师标准>>>

hot3.png

物化视图可以选择三种不同的刷新方式，根据不同的需求，选择不同的刷新方式。

  • Complete刷新：会删除表中的所有记录（如果是单表刷新，可能会采用Truncate的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
  • Fast刷新：采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中。
  • Force刷新：Oracle自动判断是否满足Fast刷新条件，如果满足则进行Fast刷新，否则进行Complete刷新。

不是所有的物化视图都可以进行Fast刷新，只有满足某些条件的物化视图才具有Fast刷新的能力。根据查询的不同，Fast刷新的限制条件也有所不同，下面是关于Fast刷新针对不同类型的物化视图的限制条件的总结。

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

    • 物化视图不能包含对不确定表达式的引用，如SYSDATE和ROWNUM；
    • 物化视图不能包含对LONG和LONG RAW数据类型的引用。
  • 2．针对只包含多表关联的物化视图的限制条件：

    • 必须满足所有快速刷新物化视图都满足的条件；
    • 不能包括GROUP BY语句或聚集操作；
    • 如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上；
    • 如果不包含外连接，那么WHERE语句没有限制，如果包含外连接，那么WHERE语句中只能使用AND连接，并且只能使用“＝”操作。
    • FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。
    • FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。

例如：

1 建立一个可以成功快速刷新的例子，这个物化视图只包含连接： SQL> create materialized view log on dim_a with rowid; 物化视图日志已创建。 SQL> create materialized view log on dim_b with rowid; 物化视图日志已创建。 SQL> create materialized view log on fact with rowid; 物化视图日志已创建。 SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id; 物化视图已创建。

  • 3．针对聚集的物化视图的限制条件：

    • 必须满足所有快速刷新物化视图都满足的条件；
    • 物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：
    • 包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；
    • 必须指明ROWID和INCLUDING NEW VALUES；
    • 如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。
    • 允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；
    • 必须指定COUNT(*)；
    • 如果指明了除COUNT之外的聚集函数，则COUNT(expression)也必须存在；比如：包含SUM(a)，则必须同时包含COUNT(a)。
    • 如果指明了VARIANCE(expression)或STDDEV(expression)，除了COUNT(expression)外，SUM(expression)也必须指明；Oracle推荐同时包括SUM(expression*expression)。
    • SELECT列表中必须包括所有的GROUP BY列；
    • 当物化视图属于下面的某种情况，则快速刷新只支持常规DML插入和直接装载，这种类型的物化视图又称为INSERT-ONLY物化视图；

      • 物化视图包含MIN或MAX聚集函数；
      • 物化视图包含SUM(expression)，但是没有包括COUNT(expression)；
      • 物化视图没有包含COUNT(*)。
      • 注意：如果建立了这种物化视图且刷新机制是ON COMMIT的，则会存在潜在的问题。当出现了UPDATE或DELETE语句，除非手工完全刷新解决这个问题，否则物化视图至此以后都不再自动刷新，且不会报任何错误。
    • 如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；
    • 如果物化视图建立在视图或子查询上，则要求视图必须可以完全合并的。
    • 如果没有外连接，则对WHERE语句没有限制。如果包含外连接，则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图，快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。
    • 对于包含了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)。

例如：

1． 建立可以成功快速刷新的例子，这个物化视图只包含聚集： SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 物化视图日志已创建。 SQL> create materialized view mv_fact refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact group by aid; 物化视图已创建。 2． 建立快速刷新的例子，物化视图包含聚集和连接： SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 物化视图日志已创建。 SQL> create materialized view log on dim_a with rowid, sequence (id, name) 2 including new values; 物化视图日志已创建。 SQL> create materialized view mv_fact refresh fast on commit as 2 select name, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact, dim_a where aid = dim_a.id group by name; 物化视图已创建。

  • 4．针对包含UNION ALL的物化视图的限制条件

    • UNION ALL操作必须在查询的顶层。可以有一种情况例外：UNION ALL在第二层，而第一层的查询语句为SELECT * FROM；
    • 被UNION ALL操作连接在一起的每个查询块都应该满足快速刷新的限制条件；
    • SELECT列表中必须包含一列维护列，叫做UNION ALL标识符，每个UNION ALL分支的标识符列应包含不同的常量值；
    • 不支持外连接、远端数据库表和包括只允许插入的聚集物化视图定义查询；
    • 不支持基于分区改变跟踪（PCT）的刷新；
    • 数据库的兼容性设置应设置为9.2.0。

例如：

1．建立一个符合快速刷新条件的UNION ALL物化视图 SQL> create materialized view log on dim_a with rowid; 物化视图日志已创建。 SQL> create materialized view log on dim_b with rowid; 物化视图日志已创建。 SQL> create materialized view log on fact with rowid; 物化视图日志已创建。 SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid row_id, a.name name, num, 'a' marker 3 from fact f, dim_a a 4 where f.aid = a.id 5 union all 6 select f.rowid f_rowid, b.rowid row_id, b.name name, num, 'b' marker 7 from fact f, dim_b b 8 where f.bid = b.id; 物化视图已创建。

  • 5．针对嵌套物化视图的限制条件

    • 嵌套物化视图的每层都必须满足快速刷新的限制条件；
    • 对于同时包含聚集和连接的嵌套物化视图，不支持ON COMMIT的快速刷新。

例如：

SQL> create materialized view log on dim_a with rowid; 物化视图日志已创建。 SQL> create materialized view log on dim_b with rowid; 物化视图日志已创建。 SQL> create materialized view log on fact with rowid; 物化视图日志已创建。 SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id; 物化视图已创建。 SQL> create materialized view log on mv_fact with rowid, sequence (a_name, num) 2 including new values; 物化视图日志已创建。 SQL> create materialized view mv_mv_fact refresh fast on commit as 2 select a_name, count(*) count, count(num) count_num, sum(num) sum_num 3 from mv_fact 4 group by a_name; 物化视图已创建。

转载于:https://my.oschina.net/u/729507/blog/88635