刷新物化视图

更新基表的数据可能会导致物化视图的数据与基表的数据不一致，为了维护物化视图的数据，OceanBase 数据库会通过刷新物化视图来实现。

OceanBase 数据库支持的物化视图刷新策略支持全量刷新和增量刷新，并且支持手动调度刷新和自动调度刷新。

全量刷新

OceanBase 数据库使用异地刷新的方式进行全量刷新，即创建一个隐藏表，在隐藏表上执行刷新语句，然后切换原表和隐藏表。因此，全量刷新操作需要额外的空间，并且会全量重建索引（如果有的话）。

全量刷新注意事项

• 全量刷新可能是一个非常耗时的过程，尤其是在需要读取和处理大量数据的情况下。因此，在执行全量刷新之前，您应该始终考虑处理全量刷新所需的时间。
• 当前基表的列与物化视图对应的列类型匹配，就符合全量刷新的条件。如果不匹配，将无法进行全量刷新。
• 如果一个物化视图进行了全量刷新，那么依赖它的物化视图（嵌套物化视图）在后续进行增量刷新前必须先进行一次全量刷新，否则会报错。

增量刷新

增量刷新的物化视图目前支持单表聚合、多表关联以及多表关联聚合的 SQL 语句，对于不满足这三类场景的 SQL 语句，暂不支持增量刷新，增量刷新 SQL 语句要求详见下述介绍。

单表聚合增量刷新基本要求

• 不支持使用聚合函数 MAX 和 MIN 进行增量更新。

• 不支持内联视图、UNION 和子查询等复杂场景的增量更新。

• 不支持包含不稳定输出值的表达式，如 ROWNUM 和 RAND 等。

• 不支持 ROLLUP 和 HAVING。

• 不支持 ORDER BY。

• 不支持窗口函数。

• 如果查询中包含 DISTINCT，那么可增量更新的物化视图中的输出列应该是唯一的，因此在这种情况下，可以直接禁止使用 DISTINCT，或者将其移除。

• 不带 GROUP BY 的语句必须是标量聚合（Scalar Aggregate）。

• 对于 GROUP BY 场景的物化视图，仅支持 SUM 和 COUNT 等聚合函数，且聚合函数中只能使用简单的列。GROUP BY 的要求如下：

  聚合函数	SELECT 子句中必须包含依赖列
  COUNT( expr )	N/A
  SUM ( expr )	COUNT( expr ) 或 expr 非 null
  AVG ( expr )	SUM ( expr ),COUNT( expr )
  STDDEV ( expr )	SUM ( expr ),COUNT( expr ),SUM ( expr * expr )
  VARIANCE ( expr )	SUM ( expr ),COUNT( expr ),SUM ( expr * expr )
  其它可拆分为 SUM 及 COUNT 的聚合函数... (计算方式变化，可能引起精度变化)	SUM (col1),COUNT(col1)

  1. GROUP BY 子句必须是标准的 GROUP BY 语法，不支持 ROLLUP 和 HAVING。
  2. SELECT 子句中必须包含所有的 GROUP BY 列。
  3. 聚合函数不包含 DISTINCT 关键字。
  4. SELECT 子句中除了包含聚合函数列外，还必须包含对应聚合函数的依赖列和 COUNT(*) 列，如使用聚合函数 SUM(expr)，还需要包含 COUNT(*) 以及 COUNT(expr) 列。

单表聚合增量刷新示例

1. 创建表 test_tbl1。

   CREATE TABLE test_tbl1 (col1 NUMBER PRIMARY KEY, col2 NUMBER, col3 NUMBER, col4 NUMBER);
   

2. 在 test_tbl1 表上创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON test_tbl1
     WITH SEQUENCE (col2, col3) INCLUDING NEW VALUES;
   

3. 创建刷新方法是增量刷新的物化视图。

   • 创建名为 mv1_test_tbl1 的物化视图。指定物化视图的刷新方法为增量刷新，并且可以根据需要手动触发刷新操作；物化视图的查询部分是从表 test_tbl1 中选择 col2 列，并计算 count(*)、count(col3) 和 sum(col3) 的聚合结果，分组依据是 col2 列的值。

     CREATE MATERIALIZED VIEW mv1_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, count(*) cnt, count(col3) cnt_col3, sum(col3) sum_col3
          FROM test_tbl1
          GROUP BY col2;
     

   • 创建名为 mv2_test_tbl1 的物化视图。指定物化视图的刷新方法为增量刷新，并且可以根据需要手动触发刷新操作；物化视图的查询部分是从表 test_tbl1 中计算 count(*)、count(col3) 和 sum(col3) 的聚合结果。

     CREATE MATERIALIZED VIEW mv2_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT count(*) cnt, count(col3) cnt_col3, sum(col3) sum_col3
          FROM test_tbl1;
     

   • 创建名为 mv3_test_tbl1 的物化视图。指定物化视图的刷新方法为增量刷新，并且可以根据需要手动触发刷新操作；物化视图的查询部分是从表 test_tbl1 中计算 count(col3) 和 sum(col3) 的结果。

     CREATE MATERIALIZED VIEW mv3_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT count(col3) cnt_col3, sum(col3) sum_col3
          FROM test_tbl1;
     

   • 创建名为 mv4_test_tbl1 的物化视图。指定物化视图的刷新方法为增量刷新，并且可以根据需要手动触发刷新操作；物化视图的查询部分是从表 test_tbl1 中选择 col2 和 col3 列，并计算 count(*)、count(col3) 和 sum(col3) 的聚合结果，以 col2 和 col3 作为分组依据。

     CREATE MATERIALIZED VIEW mv4_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, col3, count(*) cnt, count(col3) cnt_col3, sum(col3) sum_col3
          FROM test_tbl1
          GROUP BY col2, col3;
     

   • 创建名为 mv5_test_tbl1 的物化视图。指定物化视图的刷新方法为增量刷新，并且可以根据需要手动触发刷新操作；物化视图的查询部分是从表 test_tbl1 中选择 col2 列，并计算 count(*)、count(col3)、sum(col3)、avg(col3) 等聚合结果，同时还计算了一些自定义的列 calcol1 和 calcol2，分组依据是 col2 列的值。

     CREATE MATERIALIZED VIEW mv5_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, count(*) cnt, count(col3) cnt_col3, sum(col3) sum_col3, avg(col3) avg_col3, avg(col3) * sum(col3)/col2 calcol1, col2+sum(col3) calcol2
          FROM test_tbl1
          GROUP BY col2;
     

   • 创建名为 mv6_test_tbl1 的物化视图。指定物化视图的刷新方法为增量刷新，并且可以根据需要手动触发刷新操作；物化视图的查询部分是从表 test_tbl1 中选择 col2 列，并计算 count(*)、count(col3)、sum(col3)、count(col3*col3)、sum(col3*col3) 和 STDDEV(col3) 的聚合结果，分组依据是 col2 列的值。

     CREATE MATERIALIZED VIEW mv6_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, count(*) cnt, count(col3) cnt_col3, sum(col3) sum_col3, count(col3*col3) cnt_col3_2, sum(col3*col3) sum_col3_2, STDDEV(col3) stddev_col3
          FROM test_tbl1
          GROUP BY col2;
     

多表连接增量刷新基本要求

1. FROM 表必须为基表，不能为内联视图、视图等。
2. FROM 表至少包含两张表，各表间仅存在内连接。
3. FROM 表均存在主键，且主键在 SELECT 中输出。
4. FROM 表均创建 mlog，且使视图中使用列均在 mlog 中存在。
5. 视图定义中不存在子查询。
6. 视图定义中不存在 ROLLUP、HAVING、WINDOW FUNCTION、DISTINCT、ORDER BY、LIMIT 和 FETCH 等子句。
7. 视图定义中不能使用 ROWNUM、RAND 和 SYSDATE 等不稳定输出值的表达式。

多表连接增量刷新示例

1. 创建基表 t1 和 t2。

   CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
   

   CREATE TABLE t2(c1 INT PRIMARY KEY, c4 INT, c5 INT);
   

2. 在表 t1 和表 t2 上创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON t1 WITH PRIMARY KEY, ROWID, SEQUENCE (c2) INCLUDING NEW VALUES;
   

   CREATE MATERIALIZED VIEW LOG ON t2 WITH PRIMARY KEY, ROWID, SEQUENCE (c4) INCLUDING NEW VALUES;
   

3. 创建表 t1 和表 t2 连接的增量刷新的物化视图 mv1_t1_t2。

   CREATE MATERIALIZED VIEW mv1_t1_t2
     REFRESH FAST
     AS SELECT t1.c1 t1c1, t1.c2, t2.c1 t2c1, t2.c4
       FROM t1 JOIN t2 ON t1.c1=t2.c1;
   

为物化视图及其依赖基表创建索引示例如下：

1. （可选）使用以下语句删除相关测试数据。

   如果没有以下数据库对象可以跳过此步骤。

   DROP MATERIALIZED VIEW LOG ON t1;
   DROP TABLE t1;
   DROP MATERIALIZED VIEW LOG ON t2;
   DROP TABLE t2;
   DROP MATERIALIZED VIEW rt_mv1;
   

2. 使用以下语句创建表 t1 和索引 idx_t1_c2。

   CREATE TABLE t1(c1 INT GENERATED BY DEFAULT AS IDENTITY, c2 INT, c3 INT, c4 INT, c5 INT, PRIMARY KEY(c1));
   CREATE INDEX idx_t1_c2 ON t1(c2);
   

3. 使用以下语句创建表 t2 和索引 idx_t2_c3。

   CREATE TABLE t2(c1 INT GENERATED BY DEFAULT AS IDENTITY, c2 INT, c3 INT, c4 INT, c5 INT, PRIMARY KEY(c1));
   CREATE INDEX idx_t2_c3 ON t2(c3);
   

4. 使用以下语句在 t1 和 t2 表上分别创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON t1 WITH PRIMARY KEY, ROWID, SEQUENCE (c2, c3, c4) INCLUDING NEW VALUES;
   CREATE MATERIALIZED VIEW LOG ON t2 WITH PRIMARY KEY, ROWID, SEQUENCE (c2, c3, c4) INCLUDING NEW VALUES;
   

5. 使用以下语句创建实时物化视图 rt_mv1。

   CREATE MATERIALIZED VIEW rt_mv1
     NEVER REFRESH
     ENABLE ON QUERY COMPUTATION
     DISABLE QUERY REWRITE
     AS SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1, t1.c2 AS t1_c2, t2.c2 AS t2_c2, t1.c3 AS t1_c3, t2.c3 AS t2_c3 
         FROM t1, t2
         WHERE t1.c2 = t2.c3;
   

6. 使用以下语句为物化视图中各基表主键列创建索引。

   CREATE INDEX idx_mv_t1_c1 ON rt_mv1(t1_c1);
   CREATE INDEX idx_mv_t2_c1 ON rt_mv1(t2_c1);
   

多表聚合增量刷新基本要求

多表聚合增量刷新基本要求是上述 单表聚合增量刷新基本要求 和 多表连接增量刷新基本要求 的并集。不过其中与多表连接要求不同的点：FROM 表均存在主键，但是主键字段不要求在 SELECT 中输出。

多表聚合增量刷新示例

1. 创建基表 t3 和 t4。

   CREATE TABLE t3(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1));
   

   CREATE TABLE t4(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1));
   

2. 在表 t3 和表 t4 上创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON t3 WITH PRIMARY KEY, ROWID, SEQUENCE(c2, c3, c4) INCLUDING NEW VALUES;
   

   CREATE MATERIALIZED VIEW LOG ON t4 WITH PRIMARY KEY, ROWID, SEQUENCE(c2, c3, c4) INCLUDING NEW VALUES;
   

3. 创建表 t3 和表 t4 连接聚合增量刷新的实时物化视图 mv1_t3_t4。

   CREATE MATERIALIZED VIEW mv1_t3_t4
     REFRESH FAST
     ENABLE ON QUERY COMPUTATION
     AS SELECT t3.c1,
         COUNT(*) cnt,
         COUNT(t4.c4) cnt_c4,
         SUM(t4.c4) sum_c4,
         AVG(t4.c4) avg_c4
       FROM t3, t4
       WHERE t3.c2 = t4.c3
       GROUP BY t3.c1;
   

物化视图刷新并行度控制机制

OceanBase 数据库提供了物化视图刷新并行度控制机制：只要用户显式为刷新操作指定了并行度，就以用户指定的值为准；当未显式指定并行度时，可以通过配置系统变量 mview_refresh_dop，以 Session（会话）上的系统变量值为准刷新物化视图。

设置 mview_refresh_dop

mview_refresh_dop 是 OceanBase 数据库中的一个系统变量，用于控制物化视图刷新操作的默认并行度。通过合理设置其值，可以显著提升刷新效率，优化数据库性能。

当设置 mview_refresh_dop 为 0 或 1 时，表示不使用并行刷新能力。有关系统变量 mview_refresh_dop 的详细介绍信息，参见 mview_refresh_dop。

示例如下：

• 将当前 Session 并行度设置为 5。

  SET mview_refresh_dop = 5;
  

• 将全局 Session 并行度设置为 5。

  SET GLOBAL mview_refresh_dop = 5;
  

  注意

  设置 Global 级别的变量对当前 Session 无效，需要重新登录建立新的 Session 才会生效。

查看物化视图并行度信息

• 使用视图 DBA_MVIEWS 查询为物化视图设定的后台刷新并行度。

  说明

  视图 DBA_MVIEWS 查看物化视图并行度只能查看针对物化视图指定的的并行度。

  • 字段 REFRESH_DOP 为非 0 时，物化视图后台刷新任务将使用 REFRESH_DOP 对应的并行度。
  • 当 REFRESH_DOP 为 0 时，将使用设置 Global（全局）级别的 mview_refresh_dop 的值。

  示例如下：

  1. 创建物化视图 mv0_t1。

     CREATE MATERIALIZED VIEW mv0_t1
         REFRESH COMPLETE ON DEMAND
         START WITH current_date
             NEXT current_date + INTERVAL '10' SECOND
         AS SELECT c1, c2
             FROM t1;
     

  2. 查看物化视图 mv0_t1 设定的后台刷新并行度。

     SELECT OWNER, MVIEW_NAME,REFRESH_DOP
     FROM SYS.DBA_MVIEWS
     WHERE OWNER = 'SYS'
     AND MVIEW_NAME = 'MV0_T1';
     

     返回结果如下：

     +-------+------------+-------------+
     | OWNER | MVIEW_NAME | REFRESH_DOP |
     +-------+------------+-------------+
     | SYS   | MV0_T1     |           0 |
     +-------+------------+-------------+
     1 row in set
     

  3. 修改物化视图 mv0_t1 并行度为 8。

     ALTER MATERIALIZED VIEW mv0_t1 PARALLEL 8;
     

  4. 查看物化视图设定的后台刷新并行度。

     SELECT OWNER, MVIEW_NAME,REFRESH_DOP
     FROM SYS.DBA_MVIEWS
     WHERE OWNER = 'SYS'
     AND MVIEW_NAME = 'MV0_T1';
     

     返回结果如下：

     +-------+------------+-------------+
     | OWNER | MVIEW_NAME | REFRESH_DOP |
     +-------+------------+-------------+
     | SYS   | MV0_T1     |           8 |
     +-------+------------+-------------+
     1 row in set
     

• 使用视图 DBA_MVREF_RUN_STATS 查询物化视图的历史刷新并行度。

  示例如下：

  SELECT REFRESH_ID, MVIEWS, PARALLELISM
  FROM SYS.DBA_MVREF_RUN_STATS
  WHERE MVIEWS = 'SYS.MV0_T1'
  ORDER BY REFRESH_ID;
  

  返回结果如下：

  +------------+------------+-------------+
  | REFRESH_ID | MVIEWS     | PARALLELISM |
  +------------+------------+-------------+
  |    6752103 | SYS.MV0_T1 |           5 |
  |    6752733 | SYS.MV0_T1 |           5 |
  |    6753371 | SYS.MV0_T1 |           5 |
  |    6753985 | SYS.MV0_T1 |           8 |
  |    6754618 | SYS.MV0_T1 |           8 |
  |    6755249 | SYS.MV0_T1 |           8 |
  +------------+------------+-------------+
  6 rows in set
  

手动刷新物化视图

当物化视图的刷新模式是 ON DEMAND，可以使用 DBMS_MVIEW 包进行手动刷新。对于定义为增量刷新的物化视图，手动刷新时可以指定为全量刷新。

使用 REFRESH 刷新物化视图

DBMS_MVIEW.REFRESH (
   list                   IN     VARCHAR2,                  -- 物化视图名称, 暂时不支持多张物化视图
   method                 IN     VARCHAR2       := NULL,    -- 刷新选项
                                                            --   f 快速刷新
                                                            --   ? 强制刷新
                                                            --   C|c 完全刷新
                                                            --   A|a 始终刷新，等价于C
   ----------- 以下参数都不支持, 只为兼容Oracle ----------------
   rollback_seg           IN     VARCHAR2       := NULL,
   push_deferred_rpc      IN     BOOLEAN        := true,
   refresh_after_errors   IN     BOOLEAN        := false,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true, 
   nested                 IN     BOOLEAN        := false,
   out_of_place           IN     BOOLEAN        := false,
   skip_ext_data          IN     BOOLEAN        := false,
   ---------------------------------------------------------
   refresh_parallel       IN     BINARY_INTEGER := 0);       -- 物化视图刷新并行度, OB独有的参数

示例如下：

1. 向表 test_tbl1 中插入 3 条数据。

   INSERT INTO test_tbl1 VALUES (1, 1, 1, 1),(2, 2, 2, 2),(3, 3, 3, 3);
   

2. 查看物化视图 mv1_test_tbl1 信息。

   SELECT * FROM mv1_test_tbl1;
   

   返回结果如下：

   Empty set
   

3. 使用 DBMS_MVIEW.REFRESH 手动刷新物化视图 mv1_test_tbl1。

   • 使用物化视图设置的刷新选项刷新物化视图：

     CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1');
     

   • 指定刷新选项刷新物化视图：

     CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1', 'c');
     

4. 再次查看物化视图 mv1_test_tbl1 信息。

   SELECT * FROM mv1_test_tbl1;
   

   返回结果如下：

   +------+------+----------+----------+
   | COL2 | CNT  | CNT_COL3 | SUM_COL3 |
   +------+------+----------+----------+
   |    3 |    1 |        1 |        3 |
   |    2 |    1 |        1 |        2 |
   |    1 |    1 |        1 |        1 |
   +------+------+----------+----------+
   3 rows in set
   

手动刷新物化视图刷新并行度

对于手动刷新物化视图，可以通过设置系统变量 mview_refresh_dop 为物化视图刷新操作设置默认并行度。

也可以在调用 DBMS_MVIEW.REFRESH 函数时，通过指定 refresh_parallel 显式设置本次刷新的并行度。

示例如下：

1. 将当前 Session 并行度设置为 5。

   SET mview_refresh_dop = 5;
   

2. 手动刷新物化视图：

   • 显式指定刷新并行度为 8，本次刷新并行度为 8。

     CALL DBMS_MVIEW.REFRESH('mv1', 'c', 8);
     

   • 未显式指定刷新并行度，以 Session 变量值为准，本次刷新并行度为 5。

     CALL DBMS_MVIEW.REFRESH('mv1', 'c');
     

自动刷新物化视图

创建物化视图的时候，如果指定了START WITH datetime_expr 和 NEXT datetime_expr 子句，当满足要求时，系统会为物化视图创建后台自动刷新任务。

自动刷新物化视图刷新并行度

在后台自动刷新物化视图时，可以通过以下两种方式来指定并行度：

1. 创建物化视图时指定并行度（Table DOP）。

   示例如下：

   CREATE MATERIALIZED VIEW mv_t1
       PARALLEL 8
       REFRESH COMPLETE ON DEMAND
           START WITH current_date
               NEXT current_date + INTERVAL '10' SECOND
           AS SELECT c1, c2
              FROM t1;
   

2. 设置全局 Session 变量 mview_refresh_dop 作为刷新并行度。

   自动刷新操作通过内部 Session 进行刷新，需要设置 Global（全局）级别的 mview_refresh_dop 才能对后台 Session 生效。

   注意

   如果创建物化视图时未显式指定并行度，且 mview_refresh_dop 变量为 0 或 1 时，后台刷新任务不开启并行刷新。

   示例如下：

   1. 将全局 Session 并行度设置为 5。

      SET GLOBAL mview_refresh_dop = 5;
      

   2. 创建物化视图：

      • 创建物化视图时指定并行度为 8，后台刷新任务会使用 8 作为刷新并行度。

        CREATE MATERIALIZED VIEW mv1_t1
            PARALLEL 8
            REFRESH COMPLETE ON DEMAND
                 START WITH current_date
                     NEXT current_date + INTERVAL '10' SECOND
            AS SELECT c1, c2
            FROM t1;
        

      • 创建物化视图时未指定并行度，后台刷新任务会使用变量 mview_refresh_dop 的值 5 作为刷新并行度。

        CREATE MATERIALIZED VIEW mv2_t1
            REFRESH COMPLETE ON DEMAND
                 START WITH current_date
                     NEXT current_date + INTERVAL '10' SECOND
            AS SELECT c1, c2
            FROM t1;
        

嵌套物化视图刷新

嵌套物化视图的刷新规则

嵌套物化视图所支持的刷新方式和非嵌套物化视图一样，都包括全量与增量刷新。虽然刷新一个嵌套物化视图只需要用到其所直接依赖的用户表与物化视图（以及它们的 mlog），但是由于嵌套物化视图是基于其他物化视图所构建，因此其数据的一致性会依赖于其他物化视图。这意味着如果想通过刷新来让某个嵌套物化视图的数据保持最新，需要先保证其所依赖的其他物化视图数据是最新的，即需要先对它们进行刷新。

例如下图，物化视图 mv1 基于表 tbl1 和表 tbl2 构建，物化视图 mv2 则基于物化视图 mv1 和表 tbl3 构建，物化视图 mv3 基于物化视图 mv1 和物化视图 mv2 构建。按照物化视图 mv1、物化视图 mv2、物化视图 mv3 的顺序进行刷新的情况下，可以保证嵌套物化视图整体数据的一致性。否则如果先刷新物化视图 mv2，再刷新物化视图 mv1，则物化视图 mv2 的数据并不是最新（落后于物化视图 mv1）；同理，如果先刷新物化视图 mv3，再刷新物化视图 mv2，则物化视图 mv3 的数据也不是最新（落后于物化视图 mv2）。

嵌套物化视图刷新示例

如果一个物化视图进行了全量刷新，那么依赖它的物化视图在后续进行增量刷新前必须先进行一次全量刷新，否则会报错。

示例如下：

1. 创建表 tbl1 并插入一条数据。

   CREATE TABLE tbl1(id INT, name VARCHAR2(30), PRIMARY KEY(id));
   

   INSERT INTO tbl1 VALUES (1, 'jack');
   

2. 创建表 tbl2 并插入一条数据。

   CREATE TABLE tbl2(id INT, age INT, PRIMARY KEY(id));
   

   INSERT INTO tbl2 VALUES (1, 21);
   

3. 在表 tbl1 和表 tbl2 上创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON tbl1 WITH PRIMARY KEY (name) INCLUDING NEW VALUES;
   

   CREATE MATERIALIZED VIEW LOG ON tbl2 WITH PRIMARY KEY (age) INCLUDING NEW VALUES;
   

4. 基于 tbl1 和 tbl2 创建物化视图 mv1。

   CREATE MATERIALIZED VIEW mv1 (PRIMARY KEY (id1, id2))
       REFRESH FAST ON DEMAND
       AS SELECT tbl1.id id1, tbl2.id id2, tbl1.NAME, tbl2.AGE
           FROM tbl1, tbl2
           WHERE tbl1.id = tbl2.id;
   

5. 在物化视图 mv1上创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON mv1 WITH PRIMARY KEY (name, age) INCLUDING NEW VALUES;
   

6. 基于物化视图 mv1 创建物化视图（嵌套物化视图） mv2。

   CREATE MATERIALIZED VIEW mv2
       REFRESH FAST
       AS SELECT COUNT(*) cnt, COUNT(AGE) age_cnt, SUM(AGE) age_sum
           FROM mv1;
   

7. 查看物化视图 mv1 的数据。

   SELECT * FROM mv1;
   

   返回结果如下：

   +------+------+------+------+
   | ID1  | ID2  | NAME | AGE  |
   +------+------+------+------+
   |    1 |    1 | jack |   21 |
   +------+------+------+------+
   1 row in set
   

8. 查看物化视图 mv2 的数据。

   SELECT * FROM mv2;
   

   返回结果如下：

   +------+---------+---------+
   | CNT  | AGE_CNT | AGE_SUM |
   +------+---------+---------+
   |    1 |       1 |      21 |
   +------+---------+---------+
   1 row in set
   

9. 向表 tbl1 和表 tbl2 中各插入一条数据。

   INSERT INTO tbl1 VALUES (2, 'rose');
   

   INSERT INTO tbl2 VALUES (2, 19);
   

10. 增量刷新物化视图 mv1。

    CALL dbms_mview.refresh('mv1', 'f');
    

11. 查看物化视图 mv1 的数据。

    SELECT * FROM mv1;
    

    返回结果如下：

    +------+------+------+------+
    | ID1  | ID2  | NAME | AGE  |
    +------+------+------+------+
    |    1 |    1 | jack |   21 |
    |    2 |    2 | rose |   19 |
    +------+------+------+------+
    2 rows in set
    

12. 增量刷新物化视图 mv2。

    CALL dbms_mview.refresh('mv2', 'f');
    

13. 查看物化视图 mv2 的数据。

    SELECT * FROM mv2;
    

    返回结果如下：

    +------+---------+---------+
    | CNT  | AGE_CNT | AGE_SUM |
    +------+---------+---------+
    |    2 |       2 |      40 |
    +------+---------+---------+
    1 row in set
    

14. 再次向表 tbl1 和表 tbl2 中各插入一条数据。

    INSERT INTO tbl1 VALUES (3, 'mary');
    

    INSERT INTO tbl2 VALUES (3, 25);
    

15. 全量刷新物化视图 mv1。

    CALL dbms_mview.refresh('mv1', 'c');
    

16. 再次查看物化视图 mv1 的数据。

    SELECT * FROM mv1;
    

    返回结果如下：

    +------+------+------+------+
    | ID1  | ID2  | NAME | AGE  |
    +------+------+------+------+
    |    1 |    1 | jack |   21 |
    |    2 |    2 | rose |   19 |
    |    3 |    3 | mary |   25 |
    +------+------+------+------+
    3 rows in set
    

17. 再次增量刷新物化视图 mv2。

    CALL dbms_mview.refresh('mv2', 'f');
    

    返回结果如下：

    OBE-12052: cannot fast refresh materialized view
    at package body oceanbase.DBMS_MVIEW.DO_REFRESH , line : 54, col : 1
    at oceanbase.DBMS_MVIEW.REFRESH , line : 72, col : 1
    

    注意

    由于 mv1 经历过全量刷新，因此直接对 mv2 进行增量刷新会报错，必须先进行一次全量刷新。

18. 再次查看物化视图 mv2 的数据。

    SELECT * FROM mv2;
    

    返回结果如下：

    +------+---------+---------+
    | CNT  | AGE_CNT | AGE_SUM |
    +------+---------+---------+
    |    2 |       2 |      40 |
    +------+---------+---------+
    1 row in set
    

19. 全量刷新物化视图 mv2。

    CALL dbms_mview.refresh('mv2', 'c');
    

20. 再次查看物化视图 mv2 的数据。

    SELECT * FROM mv2;
    

    返回结果如下：

    +------+---------+---------+
    | CNT  | AGE_CNT | AGE_SUM |
    +------+---------+---------+
    |    3 |       3 |      65 |
    +------+---------+---------+
    1 row in set
    

实时嵌套物化视图示例

如果一个物化视图（嵌套物化视图）是实时物化视图，则需要先对下层物化视图进行增量刷新来更新 mlog。由于实时物化视图的查询结果是通过模拟 mlog 的方式去查询下层物化视图和自身结果进行合并。因此，需要先对下层物化视图进行增量刷新来更新 mlog，以此保证实时物化视图的数据是最新的。

示例如下：

1. 创建表 tbl3。

   CREATE TABLE tbl3(col1 INT, col2 INT, col3 INT);
   

2. 在表 tbl3 上创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON tbl3 WITH PRIMARY KEY, ROWID, SEQUENCE (col1, col2, col3) INCLUDING NEW VALUES;
   

3. 基于表 tbl3 创建物化视图 mv_tbl3_1。

   CREATE MATERIALIZED VIEW mv_tbl3_1
       REFRESH COMPLETE ON DEMAND
       ENABLE ON QUERY COMPUTATION
       AS SELECT col1, col2, col3, COUNT(*) AS cnt
           FROM tbl3
           GROUP BY col1, col2, col3;
   

4. 在物化视图 mv_tbl3_1 上创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON mv_tbl3_1 WITH PRIMARY KEY, ROWID, SEQUENCE (col1, col2, col3) INCLUDING NEW VALUES;
   

5. 基于物化视图 mv_tbl3_1 创建物化视图 mv_tbl3_2。

   CREATE MATERIALIZED VIEW mv_tbl3_2
       REFRESH COMPLETE ON DEMAND
       ENABLE ON QUERY COMPUTATION
       AS SELECT col1, col2, col3, COUNT(*) AS cnt
           FROM mv_tbl3_1
           GROUP BY col1, col2, col3;
   

6. 在物化视图 mv_tbl3_2 上创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON mv_tbl3_2 WITH PRIMARY KEY, ROWID, SEQUENCE (col1, col2, col3) INCLUDING NEW VALUES;
   

7. 基于物化视图 mv_tbl3_2 创建实时物化视图 mv_tbl3_3。

   CREATE MATERIALIZED VIEW mv_tbl3_3
       REFRESH COMPLETE ON DEMAND
       ENABLE ON QUERY COMPUTATION
       AS SELECT col1, col2, col3, COUNT(*) AS cnt
           FROM mv_tbl3_2
           GROUP BY col1, col2, col3;
   

8. 向表 tbl3 中插入一条数据。

   INSERT INTO tbl3 VALUES(1, 1, 1);
   

9. 查看表 tbl3 的数据。

   SELECT * FROM tbl3;
   

   返回结果如下：

   +------+------+------+
   | COL1 | COL2 | COL3 |
   +------+------+------+
   |    1 |    1 |    1 |
   +------+------+------+
   1 row in set
   

10. 查看物化视图 mv_tbl3_1 的数据。

    SELECT * FROM mv_tbl3_1;
    

    返回结果如下：

    +------+------+------+------+
    | COL1 | COL2 | COL3 | CNT  |
    +------+------+------+------+
    |    1 |    1 |    1 |    1 |
    +------+------+------+------+
    1 row in set
    

11. 查看物化视图 mv_tbl3_2 的数据。

    SELECT * FROM mv_tbl3_2;
    

    返回结果如下：

    Empty set
    

12. 查看物化视图 mv_tbl3_3 的数据。

    SELECT * FROM mv_tbl3_3;
    

    返回结果如下：

    Empty set
    

13. 增量刷新物化视图 mv_tbl3_1。

    CALL dbms_mview.refresh('mv_tbl3_1','f');
    

14. 再次查看物化视图 mv_tbl3_2 的数据。

    SELECT * FROM mv_tbl3_2;
    

    返回结果如下：

    +------+------+------+------+
    | COL1 | COL2 | COL3 | CNT  |
    +------+------+------+------+
    |    1 |    1 |    1 |    1 |
    +------+------+------+------+
    1 row in set
    

15. 再次查看物化视图 mv_tbl3_3 的数据。

    SELECT * FROM mv_tbl3_3;
    

    返回结果如下：

    Empty set
    

16. 增量刷新物化视图 mv_tbl3_2。

    CALL dbms_mview.refresh('mv_tbl3_2','f');
    

17. 再次查看物化视图 mv_tbl3_3 的数据。

    SELECT * FROM mv_tbl3_3;
    

    返回结果如下：

    +------+------+------+------+
    | COL1 | COL2 | COL3 | CNT  |
    +------+------+------+------+
    |    1 |    1 |    1 |    1 |
    +------+------+------+------+
    1 row in set
    

物化视图刷新统计信息

OceanBase 系统能够收集并保存物化视图刷新操作的相关统计信息，这些信息可通过特定的视图进行查询。当前和历史物化视图刷新操作的统计信息存储在数据库中。通过历史物化视图刷新统计信息，您可以了解和分析数据库中物化视图刷新性能。

物化视图刷新的统计信息作用如下：

• 报告功能：提供物化视图刷新操作的当前及历史统计概览，包括刷新执行所需的实际时间，以便跟踪和监控刷新性能。

• 诊断功能：通过详尽的当前和历史统计数据，能够有效地分析物化视图刷新的性能。例如，若物化视图的刷新耗时较长，统计数据可以帮助识别是系统负载增加还是因数据变更量增大导致的性能下降。

物化视图收集统计信息

物化视图收集统计信息。可以通过 analyze table 或者 call dbms_stats.gather_table_stats('database_name', 'table_name') 来收集统计信息。

• 更多有关收集表和列的统计信息的信息，请参见 GATHER_TABLE_STATS。

• 更多有关管理物化视图刷新操作的统计信息的收集和保留的信息，请参见 DBMS_MVIEW_STATS 概述。

展示物化视图刷新信息

相关文档

• 物化视图概述
• 物化视图日志
• 创建物化视图
• 物化视图查询改写
• 查询物化视图
• 删除物化视图
• 物化视图资源隔离
• 物化视图异常诊断