嵌套物化视图使用说明

OceanBase 数据库 V4.3.4 版本只支持在普通用户表上创建物化视图，而在数据仓库场景下，会使用物化视图来做数据加工，为了更好地支持轻量级实时数仓场景，OceanBase 数据库 V4.3.5 版本支持基于已有物化视图创建新的物化视图，即嵌套物化视图。嵌套物化视图所支持的刷新方式和非嵌套物化视图一样，都支持全量刷新与增量刷新，所以 OceanBase 数据库 V4.3.5支持基于物化视图创建物化视图日志 mlog。嵌套物化视图中数据的新鲜度与基表数据的新鲜度有关，为了保证上层物化视图数据的新鲜度，用户需要先刷新底层的物化视图。

本文主要介绍嵌套物化视图的创建方法、刷新机制及功能限制。

详细说明

嵌套物化视图所支持的刷新方式和非嵌套物化视图一样，都包括全量刷新与增量刷新。虽然刷新一个嵌套物化视图只需要用到其所直接依赖的用户表与物化视图（以及它们的 mlog），但是由于嵌套物化视图是基于其它物化视图所构建，因此其数据的一致性会依赖于其它物化视图。这意味着如果想通过刷新来让某个嵌套物化视图的数据保持最新，需要先保证其所依赖的其它物化视图的数据是最新的，即需要先对它们进行刷新，依此类推。 以下图所示的嵌套物化视图为例，上层的物化视图基于下层的物化视图构建。则用户按照物化视图1、物化视图 2、物化视图3的顺序进行刷新的情况下，可以保证嵌套物化视图整体数据的一致性。否则如果用户先刷新物化视图2，再刷新物化视图1，则物化视图2的数据并不是最新（落后于物化视图 1）；同理，如果先刷新物化视图3，再刷新物化视图 2，则物化视图3的数据也不是最新（落后于物化视图 2）。

测试版本信息

OceanBase 数据库 V4.3.5 Hotfix1 版本。

功能限制

• 只支持在普通用户实体表和物化视图上创建新的物化视图，不支持在普通视图上创建新的物化视图。

• 为了支持嵌套物化视图的增量刷新，允许在物化视图上创建物化视图日志 mlog。

• 如果对一个物化视图进行了全量刷新，那么依赖它的物化视图在后续进行增量刷新前必须先进行一次全量刷新，否则会报错 OBE-12052: cannot fast refresh materialized view。

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

嵌套物化视图刷新规则

• 如果一个物化视图进行了全量刷新，那么依赖它的物化视图在后续进行增量刷新前必须先进行一次全量刷新，否则会报错 OBE-12052: cannot fast refresh materialized view。

  测试示例如下：

  -- 创建普通用户表 ta 和 tb，并插入数据
  obclient(SYS@oracle)[SYS]> create table ta (id int primary key, name varchar(30));
  Query OK, 0 rows affected (0.159 sec)
  
  obclient(SYS@oracle)[SYS]> create table tb (id int primary key, age int);
  Query OK, 0 rows affected (0.222 sec)
  
  obclient(SYS@oracle)[SYS]> insert into ta values (1, 'jack');
  Query OK, 1 row affected (0.003 sec)
  
  obclient(SYS@oracle)[SYS]> insert into tb values (1, 21);
  Query OK, 1 row affected (0.023 sec)
  
  -- 基于 ta 和 tb 创建常规的物化视图日志及普通物化视图 mv1
  obclient(SYS@oracle)[SYS]> create materialized view log on ta with primary key (name) including new values;
  Query OK, 0 rows affected (0.519 sec)
  
  obclient(SYS@oracle)[SYS]> create materialized view log on tb with primary key (age) including new values;
  Query OK, 0 rows affected (0.356 sec)
  
  obclient(SYS@oracle)[SYS]> create materialized view mv1 (primary key (id_a, id_b)) refresh fast on demand as select ta.id id_a, tb.id id_b, ta.name, tb.age from ta, tb where ta.id = tb.id;
  Query OK, 0 rows affected (1.696 sec)
  
  -- 基于mv1创建嵌套物化视图mv2
  obclient(SYS@oracle)[SYS]> create materialized view log on mv1 with primary key (name, age) including new values;
  Query OK, 0 rows affected (0.369 sec)
  
  obclient(SYS@oracle)[SYS]> create materialized view mv2 refresh fast as select count(*) cnt, count(age) age_cnt, sum(age) age_sum from mv1;
  Query OK, 0 rows affected (1.178 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv1;
  +------+------+------+------+
  | ID_A | ID_B | NAME | AGE  |
  +------+------+------+------+
  |    1 |    1 | jack |   21 |
  +------+------+------+------+
  1 row in set (0.025 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv2;
  +------+---------+---------+
  | CNT  | AGE_CNT | AGE_SUM |
  +------+---------+---------+
  |    1 |       1 |      21 |
  +------+---------+---------+
  1 row in set (0.014 sec)
  
  obclient(SYS@oracle)[SYS]> insert into ta values (2, 'rose');
  Query OK, 1 row affected (0.021 sec)
  
  obclient(SYS@oracle)[SYS]> insert into tb values (2, 19);
  Query OK, 1 row affected (0.021 sec)
  
  -- 依次增量刷新 mv1 和 mv2
  obclient(SYS@oracle)[SYS]> call dbms_mview.refresh('mv1', 'f');
  Query OK, 0 rows affected (0.072 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv1;
  +------+------+------+------+
  | ID_A | ID_B | NAME | AGE  |
  +------+------+------+------+
  |    1 |    1 | jack |   21 |
  |    2 |    2 | rose |   19 |
  +------+------+------+------+
  2 rows in set (0.001 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mlog$_mv1;
  +------+------+------+------+------------+-----------+-----------+
  | ID_A | ID_B | NAME | AGE  | SEQUENCE$$ | DMLTYPE$$ | OLD_NEW$$ |
  +------+------+------+------+------------+-----------+-----------+
  |    2 |    2 | rose |   19 |          1 | I         | N         |
  +------+------+------+------+------------+-----------+-----------+
  1 row in set (0.004 sec)
  
  obclient(SYS@oracle)[SYS]> call dbms_mview.refresh('mv2', 'f');
  Query OK, 0 rows affected (0.030 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv2;
  +------+---------+---------+
  | CNT  | AGE_CNT | AGE_SUM |
  +------+---------+---------+
  |    2 |       2 |      40 |
  +------+---------+---------+
  1 row in set (0.001 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mlog$_mv1;
  Empty set (0.001 sec)
  
  -- 对 mv1 进行增量刷新和全量刷新
  obclient(SYS@oracle)[SYS]> insert into ta values (3, 'mary');
  Query OK, 1 row affected (0.001 sec)
  
  obclient(SYS@oracle)[SYS]> insert into tb values (3, 25);
  Query OK, 1 row affected (0.001 sec)
  
  obclient(SYS@oracle)[SYS]> call dbms_mview.refresh('mv1', 'f');
  Query OK, 0 rows affected (0.022 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv1;
  +------+------+------+------+
  | ID_A | ID_B | NAME | AGE  |
  +------+------+------+------+
  |    1 |    1 | jack |   21 |
  |    2 |    2 | rose |   19 |
  |    3 |    3 | mary |   25 |
  +------+------+------+------+
  3 rows in set (0.001 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mlog$_mv1;
  +------+------+------+------+------------+-----------+-----------+
  | ID_A | ID_B | NAME | AGE  | SEQUENCE$$ | DMLTYPE$$ | OLD_NEW$$ |
  +------+------+------+------+------------+-----------+-----------+
  |    3 |    3 | mary |   25 |          2 | I         | N         |
  +------+------+------+------+------------+-----------+-----------+
  1 row in set (0.001 sec)
  
  obclient(SYS@oracle)[SYS]> call dbms_mview.refresh('mv1', 'c');
  Query OK, 0 rows affected (1.823 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv1;
  +------+------+------+------+
  | ID_A | ID_B | NAME | AGE  |
  +------+------+------+------+
  |    1 |    1 | jack |   21 |
  |    2 |    2 | rose |   19 |
  |    3 |    3 | mary |   25 |
  +------+------+------+------+
  3 rows in set (0.016 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mlog$_mv1;
  Empty set (0.019 sec)
  
  -- 由于 mv1 经历过全量刷新，因此直接对 mv2 进行增量刷新会报错，必须先进行一次全量刷新
  obclient(SYS@oracle)[SYS]> 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
  
  obclient(SYS@oracle)[SYS]> select * from mv2;
  +------+---------+---------+
  | CNT  | AGE_CNT | AGE_SUM |
  +------+---------+---------+
  |    2 |       2 |      40 |
  +------+---------+---------+
  1 row in set (0.003 sec)
  
  -- 重新对 mv2 进行全量刷新
  obclient(SYS@oracle)[SYS]> call dbms_mview.refresh('mv2', 'c');
  Query OK, 0 rows affected (0.903 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv2;
  +------+---------+---------+
  | CNT  | AGE_CNT | AGE_SUM |
  +------+---------+---------+
  |    3 |       3 |      65 |
  +------+---------+---------+
  1 row in set (0.015 sec)
  

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

  测试示例如下：

  -- 创建普通用户表 tb 和实时物化视图 mv_tb_1 及二者的 mlog
  obclient(SYS@oracle)[SYS]> create table tb (col1 int, col2 int, col3 int);
  Query OK, 0 rows affected (0.319 sec)
  
  obclient(SYS@oracle)[SYS]> create materialized view log on tb with primary key, rowid, sequence (col1, col2, col3) including new values;
  Query OK, 0 rows affected (0.616 sec)
  
  obclient(SYS@oracle)[SYS]> create materialized view mv_tb_1 refresh complete on demand enable on query computation as select col1, col2, col3, count(*) as cnt from tb group by col1, col2, col3;
  Query OK, 0 rows affected (1.482 sec)
  
  obclient(SYS@oracle)[SYS]> create materialized view log on mv_tb_1 with primary key, rowid, sequence (col1, col2, col3) including new values;
  Query OK, 0 rows affected (0.385 sec)
  
  -- 基于 mv_tb_1 创建嵌套物化视图 mv_tb_2 及 mlog
  obclient(SYS@oracle)[SYS]> create materialized view mv_tb_2 refresh complete on demand enable on query computation as select col1, col2, col3, count(*) as cnt from mv_tb_1 group by col1, col2, col3;
  Query OK, 0 rows affected (1.185 sec)
  
  obclient(SYS@oracle)[SYS]> create materialized view log on mv_tb_2 with primary key, rowid, sequence (col1, col2, col3) including new values;
  Query OK, 0 rows affected (0.366 sec)
  
  -- 基于 mv_tb_2 创建嵌套物化视图 mv_tb_3
  obclient(SYS@oracle)[SYS]> create materialized view mv_tb_3 refresh complete on demand enable on query computation as select col1, col2, col3, count(*) as cnt from mv_tb_2 group by col1, col2, col3;
  Query OK, 0 rows affected (1.174 sec)
  
  -- 向基表插入数据，并查看各层物化视图结果
  obclient(SYS@oracle)[SYS]> insert into tb values(1,1,1);
  Query OK, 1 row affected (0.008 sec)
  
  obclient(SYS@oracle)[SYS]> select * from tb;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |    1 |    1 |    1 |
  +------+------+------+
  1 row in set (0.003 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv_tb_1;
  +------+------+------+------+
  | COL1 | COL2 | COL3 | CNT  |
  +------+------+------+------+
  |    1 |    1 |    1 |    1 |
  +------+------+------+------+
  1 row in set (0.033 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv_tb_2;
  Empty set (0.029 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv_tb_3;
  Empty set (0.032 sec)
  
  -- 对 mv_tb_1 进行增量刷新，并查看各层物化视图结果
  obclient(SYS@oracle)[SYS]> call dbms_mview.refresh('mv_tb_1','f');
  Query OK, 0 rows affected (0.362 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv_tb_2;
  +------+------+------+------+
  | COL1 | COL2 | COL3 | CNT  |
  +------+------+------+------+
  |    1 |    1 |    1 |    1 |
  +------+------+------+------+
  1 row in set (0.003 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv_tb_3;
  Empty set (0.003 sec)
  
  -- 对 mv_tb_2 进行增量刷新，并查看各层物化视图结果
  obclient(SYS@oracle)[SYS]> call dbms_mview.refresh('mv_tb_2','f');
  Query OK, 0 rows affected (0.035 sec)
  
  obclient(SYS@oracle)[SYS]> select * from mv_tb_3;
  +------+------+------+------+
  | COL1 | COL2 | COL3 | CNT  |
  +------+------+------+------+
  |    1 |    1 |    1 |    1 |
  +------+------+------+------+
  1 row in set (0.003 sec)
  

影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。

适用版本

OceanBase 数据库 V4.3.5（oceanbase-4.3.5.0-100000122024123020）及之后版本。