OceanBase 4.3.3 AP 功能解析：物化视图

OceanBase 在2024 年度发布会中正式发布了 4.3.3 GA 版本，作为 AP 场景的主推版本。本文分享 OB 4.3.3 AP 能力的主力功能之一 —— 物化视图。

物化视图特性概述">物化视图特性概述

OceanBase的AP功能具备众多关键特性。之前我们已经介绍了外部表、JSON类型、RoaringBitmap类型以及列存表，接下来将聚焦于物化视图（Materialized View）的介绍。

传统的视图并不存储实际数据，而物化视图则是将视图查询的结果进行了持久化存储。尽管我无法确认Oracle是否为首个推出物化视图的数据库系统，但OceanBase的物化视图功能无疑是在参照Oracle的基础上实现的。在OceanBase的Oracle租户中，物化视图默认遵循Oracle的兼容标准，这意味着在使用上可能会受到一些与Oracle相似的限制。然而，在OceanBase的MySQL租户中，物化视图的实现则相对更为灵活自由。

物化视图内部表">物化视图内部表

视图是不能存储数据的，物化视图能存储数据，是因为背后隐藏着一个表。在 ORACLE 里这个表可以自动创建也可以事先定义，在 OB 里这个表不用事先定义（也不支持）， OB 创建物化视图的时候会自动创建一个内部表。针对物化视图可以后期加主键、索引，也可以针对后面的内部表做某些操作。

物化视图刷新策略">物化视图刷新策略

物化视图的数据还可以理解为视图查询结果的快照。默认情况下这个数据不会主动变化，即使视图对应查询结果集发生了变化。需要物化视图的数据同步到最新查询结果集样子，需要对物化视图进行刷新操作。这个刷新可以是手动刷新，也可以是自动刷新的。自动刷新触发时机会有多种策略选择。可以是每次查询触发刷新，也可以是定时刷新（每分钟或每小时或每天等）。定时刷新的原理是调用数据库作业（SCHEDULED JOB）。

物化视图的查询语句的执行通常都很耗时，其原因一方面是视图的基表数据量很大，二是查询语句做的都是大批量数据扫描、排序和统计类操作。物化视图的刷新还有两种方式。一是全量刷新。每次全量刷新的时候是清空视图背后的表，然后重新用查询结果集填充该表。这个性能通常很慢，需要几分钟或者十几分钟、几十分钟不等。二是增量刷新，每次增量刷新的时候只需要在视图上次查询快照之后变化的数据应用到物化视图中。简单的说只要追一个增量就行。

物化视图刷新进行中时，查看物化视图读取的还是其内部表老的数据，这里并不会有等待或阻塞现象。其原理就是普通表的 MVCC 读。

物化视图增量刷新">物化视图增量刷新

增量刷新通常可以做到很快（秒级别）。增量刷新很实用，不过对视图查询语句要求很多。由于查询语句的写法是五花八门的，增量刷新只能一点点去支持。目前 OB 的物化视图要支持增量刷新的前提之一就是要求 SQL 语句里必须有 COUNT 统计语句。此外还支持 SUM、AVG、STDDEV 、VARIANCE 。支持 GROUP BY 但不支持 WITH ROLLUP 等等。这里面的限制具体看官网文档。

1730126058

此外，视图也往往有多表连接，增量刷新对视图查询中表连接用法也是有要求的。比如说要求必须是基于表的连接，表要有主键，多表之间是内连接等等。具体限制也看官网文档。

1730126067

虽然上面限制条件不多，实际上限制却是非常苛刻。业务创建可增量刷新的物化视图刚开始时失败概率往往很大，需要花不少时间去分析是否满足上面条件。后面会有示例说明。此外，随着 OB 4.3.3 后续版本的推出，这些限制条件还可能再次发生变化。

增量刷新一样可以手动刷新，也可以定时刷新。

上面物化视图增量刷新还有个要求就是视图对应的表（base table）上要创建物化视图日志。

物化视图日志">物化视图日志

物化视图日志（Materialized View Log）是一种特殊的数据库对象，用于记录在基表上发生的数据更改。它是实现物化视图增量刷新机制的关键组件。物化视图日志是关联到基表，用于捕获和存储基表上每次数据修改（插入、更新、删除）的详细信息。这些日志使得物化视图在刷新时无需重新扫描整个基表，而是只需应用自上次刷新以来记录在日志中的更改，从而提高刷新效率。

物化视图日志就好像在基表上创建了触发器（DML TRIGGER），通过触发器保存了变化的数据前后值。至于要保存哪些值，在创建物化视图日志定义时指定。建议显式指定主键、ROWID 以及物化视图中要访问的列。这里跟 ORACLE 物化视图日志会有点区别，OB 里会要求出现在增量刷新物化视图 SELECT 后的投影列都要在物化视图日志中出现。同理，随着 OB 4.3.3 后续版本的推出，这些限制也可能发生变化。

物化视图日志是物化视图增量刷新的关键，也是实时物化视图查询的关键。物化视图日志数据会在依赖该物化视图日志的手游物化视图都刷新后才会删除对应不再被需要的日志记录。

物化视图">实时物化视图

实时物化视图的效果就跟普通视图效果一样，反应的是最新的查询结果，但是性能比普通视图的要好很多。实时物化视图性能好的原理是视图基表的物化视图日志记录了基表数据的变化，视图查询时能在线计算这个变化的数据（增量数据）应用到物化视图上次结果集中。实时物化视图创建时必须指定刷新方式（全量还是增量）并开启在线计算功能（ENABLE ON QUERY COMPUTATION）。从官网功能描述上看实时物化视图的计算不会改变物化视图日志的内容。只有刷新（不管是手动的还是自动的）才会删除物化视图日志。

实时物化视图的创建条件跟物化视图增量刷新的要求是一样的，目前的功能限制还是比较苛刻的。后续版本功能限制预计会逐步放开。

物化视图测试">物化视图测试

以下测试如无特殊说明，都是在 ORACLE 租户下测试。MySQL 租户下应该也能跑，就是数据类型要自己变化一下。

数据准备

测试数据是 TPC-H 场景（Scale 为 10G ）。利用 OB 4.3.3 的 load data 或者外表功能将数据加载到 OB 表中。外表的使用参考前面文章 OB 4.3.2 外表使用体验。

物化视图示例">全量刷新的物化视图示例

全量刷新的物化视图基本上对视图查询语句没有限制。

CREATE materialized VIEW mv_orders_by_clerk
refresh complete ON demand 
AS SELECT o_clerk, count(DISTINCT o_custkey) cust_cnt, count(*) order_cnt, sum(o_totalprice) sum_prices
FROM orders 
GROUP BY o_clerk
;

查看刚创建的物化视图。

SELECT owner, mview_name, container_name, query, rewrite_enabled, refresh_mode, refresh_method, build_mode, fast_refreshable, last_refresh_type, last_refresh_date, last_refresh_end_time, on_query_computationFROM dba_mviews WHERE mview_name = 'MV_ORDERS_BY_CLERK';

1730126082

如果感兴趣的话，还可以顺便把这个物化视图对应的内部表一起查出来。下面方法需要在 sys 租户下运行。首先通过现有的物化视图查出对应的 database_id 和 table_id 。

SELECT tenant_id, database_id, table_id, table_name, table_type,data_table_id,view_definitionFROM oceanbase.__ALL_VIRTUAL_TABLE WHERE tenant_id=1002 and table_name in ('mv_orders_by_clerk','orders');

1730126093

如上图，物化视图 mv_orders_by_clerk 的 data_table_id 就对应的是它的内部表的table_id 。这个对应关系就像表的索引跟表的关系一样。紧接着再根据 table_id 就可以查出所有有联系的表/视图信息了。

SELECT tenant_id, table_id, table_name, table_type,data_table_id,view_definition
FROM oceanbase.__ALL_VIRTUAL_TABLE 
WHERE tenant_id=1002 and database_id = 500003 and table_id in (500062, 511049, 511051 )
;
;

1730126102

进一步到 ORACLE 租户里查看这个内部表 __mv_container_511049 的表结构。

obclient [TPCH]> show create table "__mv_container_511049"\G
*************************** 1. row ***************************TABLE: __mv_container_511049
CREATE TABLE: CREATE TABLE "__mv_container_511049" ("O_CLERK" CHAR(15),"CUST_CNT" NUMBER(38),"ORDER_CNT" NUMBER(38),"SUM_PRICES" NUMBER
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.013 sec)

可以看到，虽然基表orders 有主键，但是物化视图上并没有主键。即使物化视图创建语句是像下面这种写法，物化视图上也不会有主键。

create table t10(id number not null primary key,c1 varchar2(50), c2 date default sysdate, c3 varchar2(50),c4 varchar2(50));
CREATE MATERIALIZED VIEW mv_t10 refresh complete AS SELECT * FROM t10;

1730126112

这个是跟 ORACLE 数据库物化视图不一致的地方。所以接下来要给物化视图加主键、索引等。

物化视图加主键和索引示例">给物化视图加主键和索引示例

在 ORACLE 里，如果给这个物化视图 mv_t10 加主键，会报主键已经存在。上面已经解释。

SQL> alter materialized view mv_t10 add primary key(id);
alter materialized view mv_t10 add primary key(id)
*
ERROR at line 1:
ORA-02260: table can have only one primary key

在 OB 里看能不能直接给物化视图加主键。

obclient [TPCH]> alter materialized view mv_t10 add primary key(id);
OBE-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'materialized view mv_t10 add primary key(id)' at line 1
obclient [TPCH]>

直接报语法错误。跟 OB 研发交流已经做了这个功能，可能没有发布到当前这个 OB 版本里。那么我们可以通过对物化视图背后的表加主键、修改为列存模式（也就是将物化视图变为列存模式了）。

obclient [TPCH]> alter table "__mv_container_511070" add primary key(id) ;
Query OK, 0 rows affected (0.765 sec)OceanBase version for the right syntax to use near 'mv_orders_Ind1(SUM_PRICES)' at line 1
obclient [TPCH]> alter table "__mv_container_511049" add primary key (o_clerk) ;
Query OK, 0 rows affected (0.626 sec)obclient [TPCH]> alter table "__mv_container_511049" add column group (each column);
Query OK, 0 rows affected (0.757 sec)

然而，当操作物化视图的内部表后，发现物化视图就不可访问了，也不能删除。其原因是基表 DDL 后 table_id 发生变化，就跟物化视图记录的 data_table_id 不一致了，二者联系断开了，触发 OB BUG 了。

所以，这个内部表就理解一下物化视图原理即可，不要操作了（当然加个索引不会改变表的 table_id，所以是可以的）。

所以如果物化视图创建时没有加主键、做分区表或者变列存模式，后期想改的话那就重建物化视图吧。重建的时候可以主键、分区以及列存一起用上。

DROP materialized VIEW mv_orders_by_clerk;
CREATE materialized VIEW mv_orders_by_clerk(o_clerk, cust_cnt, order_cnt, sum_prices, PRIMARY KEY (o_clerk)) 
PARTITION BY hash(o_clerk) partitions 8 WITH COLUMN GROUP(EACH COLUMN)
refresh complete ON demand AS 
SELECT o_clerk, count(DISTINCT o_custkey) cust_cnt, count(*) order_cnt, sum(o_totalprice) sum_prices
FROM orders GROUP BY o_clerk
;

查看一个该物化视图的执行计划可以看到使用列存算子。

1730126135

如果给这个物化视图补充一个索引，执行计划还会变化。

1730126151

将索引位置信息继续追查到底，可以看到是加到内部表上了。

obclient [TPCH]> SELECT owner, table_name, index_name, uniqueness FROM dba_indexes WHERE table_name IN ('__mv_container_511117','mv_orders_by_clerk2');
+-------+-----------------------+--------------------------+------------+
| OWNER | TABLE_NAME            | INDEX_NAME               | UNIQUENESS |
+-------+-----------------------+--------------------------+------------+
| TPCH  | __mv_container_511117 | _OBPK_1730038162688240   | UNIQUE     |
| TPCH  | __mv_container_511117 | MV_ORDERS_BY_CLERK2_IND1 | NONUNIQUE  |
+-------+-----------------------+--------------------------+------------+
2 rows in set (0.038 sec)

物化视图日志示例">物化视图日志示例

接下来为了测试物化视图增量刷新，需要先创建一个物化视图日志。

obclient [TPCH]> CREATE materialized VIEW log ON orders WITH PRIMARY KEY ,rowid, SEQUENCE(O_CUSTKEY, O_CLERK, O_TOTALPRICE ) INCLUDING NEW VALUES ;
Query OK, 0 rows affected (0.452 sec)

更新几笔数据，看看物化视图日志内容。

1730126166

可以看到，每更新一笔记录，物化视图会有两笔记录（一笔O表示老记录，一笔N表示新记录）。

物化视图增量刷新示例">物化视图增量刷新示例

obclient [TPCH]> CREATE materialized VIEW mv_orders refresh fast AS SELECT * FROM orders;
OBE-12052: cannot fast refresh materialized view

OB 4.3.3 目前还不支持这种全字段的增量刷新的物化视图。按照文档要求写，目前支持统计语句的物化视图增量刷新。

CREATE materialized VIEW mv_orders_by_clerk3refresh fast START WITH current_date NEXT current_date + INTERVAL '1' MINUTE 
AS SELECT o_clerk, count(o_totalprice) cust_cnt, count(*) order_cnt, sum(o_totalprice) sum_prices
FROM orders GROUP BY o_clerk
;

这个语句使用了定时增量刷新（每分钟刷新一次）。为了统计 sum(o_totalprice) ，SELECT 里必须有列 count(o_totalprice) 。尽管这个显得有点诡异。此外，就是列的 count 里还不允许用 distinct 。

可以查看定时刷新的作业情况。

obclient [TPCH]> SELECT owner, mview_name, container_name, query, rewrite_enabled, refresh_mode, refresh_method, build_mode, fast_refreshable, last_refresh_type, last_refresh_date, last_refresh_end_time, on_query_computation   FROM dba_mviews WHERE mview_name like 'MV_ORDERS_BY_CLERK%';
+-------+---------------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------+----------------+------------+------------------+-------------------+---------------------+-----------------------+----------------------+
| OWNER | MVIEW_NAME          | CONTAINER_NAME      | QUERY                                                                                                                                                                                                                                       | REWRITE_ENABLED | REFRESH_MODE | REFRESH_METHOD | BUILD_MODE | FAST_REFRESHABLE | LAST_REFRESH_TYPE | LAST_REFRESH_DATE   | LAST_REFRESH_END_TIME | ON_QUERY_COMPUTATION |
+-------+---------------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------+----------------+------------+------------------+-------------------+---------------------+-----------------------+----------------------+
| TPCH  | MV_ORDERS_BY_CLERK  | MV_ORDERS_BY_CLERK  | select "TPCH"."ORDERS"."O_CLERK" AS "O_CLERK",count(distinct "TPCH"."ORDERS"."O_CUSTKEY") AS "CUST_CNT",count(*) AS "ORDER_CNT",sum("TPCH"."ORDERS"."O_TOTALPRICE") AS "SUM_PRICES" from "TPCH"."ORDERS" group by "TPCH"."ORDERS"."O_CLERK" | N               | DEMAND       | COMPLETE       | IMMEDIATE  | NULL             | COMPLETE          | 2024-10-27 21:13:38 | 2024-10-27 21:13:51   | N                    |
| TPCH  | MV_ORDERS_BY_CLERK3 | MV_ORDERS_BY_CLERK3 | select "TPCH"."ORDERS"."O_CLERK" AS "O_CLERK",count("TPCH"."ORDERS"."O_TOTALPRICE") AS "CUST_CNT",count(*) AS "ORDER_CNT",sum("TPCH"."ORDERS"."O_TOTALPRICE") AS "SUM_PRICES" from "TPCH"."ORDERS" group by "TPCH"."ORDERS"."O_CLERK"       | N               | DEMAND       | FAST           | IMMEDIATE  | NULL             | FAST              | 2024-10-27 22:33:10 | 2024-10-27 22:33:10   | N                    |
+-------+---------------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------+----------------+------------+------------------+-------------------+---------------------+-----------------------+----------------------+
2 rows in set (0.020 sec)obclient [TPCH]> SELECT owner, job_name, job_style, job_action, start_date, repeat_interval, state, last_start_date FROM DBA_SCHEDULER_JOBS WHERE job_name LIKE 'MVIEW_REFRESH$%';
+-------+----------------------------------+-----------+-----------------------------------------------------------------------+--------------------------------------+------------------------------------+-----------+--------------------------------------+
| OWNER | JOB_NAME                         | JOB_STYLE | JOB_ACTION                                                            | START_DATE                           | REPEAT_INTERVAL                    | STATE     | LAST_START_DATE                      |
+-------+----------------------------------+-----------+-----------------------------------------------------------------------+--------------------------------------+------------------------------------+-----------+--------------------------------------+
| SYS   | MVIEW_REFRESH$J_1125899908934882 | regular   | DBMS_MVIEW.refresh('TPCH.MV_ORDERS2_BY_CLERK', refresh_parallel => 1) | 2024-10-20 17:16:57.000000 pm +08:00 | current_date + INTERVAL '1' MINUTE | SCHEDULED | 2024-10-27 22:43:01.007239 pm +08:00 |
| SYS   | MVIEW_REFRESH$J_1125899922873296 | regular   | DBMS_MVIEW.refresh('TPCH.MV_ORDERS_BY_CLERK3', refresh_parallel => 1) | 2024-10-27 22:27:57.000000 pm +08:00 | current_date + INTERVAL '1' MINUTE | SCHEDULED | 2024-10-27 22:43:10.007227 pm +08:00 |
+-------+----------------------------------+-----------+-----------------------------------------------------------------------+--------------------------------------+------------------------------------+-----------+--------------------------------------+
2 rows in set (0.007 sec)

再来一个复杂的两表连接查询的物化视图增量刷新示例。

CREATE materialized VIEW log ON partsupp WITH PRIMARY KEY ,rowid, sequence( ps_availqty,  ps_supplycost, ps_comment ) INCLUDING NEW VALUES ;
CREATE materialized VIEW log ON part WITH PRIMARY KEY ,rowid, sequence(p_name, p_mfgr, p_brand, p_type, p_retailprice) INCLUDING NEW VALUES ;
CREATE MATERIALIZED VIEW mv_wide_part(ps_partkey, ps_suppkey, ps_availqty, ps_supplycost, ps_comment, p_partkey, p_name, p_mfgr, p_brand, p_type, p_retailprice, PRIMARY KEY (ps_partkey, ps_suppkey)) PARALLEL 4 PARTITION BY hash(ps_partkey) partitions 9 WITH COLUMN GROUP(EACH COLUMN)REFRESH FAST ON DEMAND ASSELECT /*+ parallel(8) */ ps.ps_partkey,  ps.ps_suppkey, ps.ps_availqty, ps.ps_supplycost, ps.ps_comment, p.p_partkey, p.p_name, p.p_mfgr, p.p_brand, p.p_type, p.p_retailprice FROM part p , partsupp ps WHERE p.p_partkey=ps.ps_partkey ;

物化视图查询 SELECT 里所有的列都必须出现在基表的物化视图日志里，否则增量刷新的物化视图创建失败。

测试脚本是先修改基表数据，然后手动刷新物化视图，再对比物化视图内容。

1730126182

物化视图示例">实时物化视图示例

CREATE MATERIALIZED VIEW mv_wide_part3(ps_partkey, ps_suppkey, ps_availqty, ps_supplycost, ps_comment, p_partkey, p_name, p_mfgr, p_brand, p_type, p_retailprice, PRIMARY KEY (ps_partkey, ps_suppkey)) PARALLEL 4 
PARTITION BY hash(ps_partkey) partitions 9 WITH COLUMN GROUP(EACH COLUMN)
NEVER REFRESH ENABLE ON QUERY COMPUTATION ENABLE QUERY REWRITE AS
SELECT /*+ parallel(8) */ ps.ps_partkey, ps.ps_suppkey, ps.ps_availqty, ps.ps_supplycost, ps.ps_comment, p.p_partkey, p.p_name, p.p_mfgr, p.p_brand, p.p_type, p.p_retailprice 
FROM part p , partsupp ps WHERE p.p_partkey=ps.ps_partkey ;

这个实时物化视图没有开启自动刷新机制，但是开通了查询改写功能。下面测试修改基表数据，观察实时物化视图查询结果是否发生变化。

1730126196

实时物化视图查询非常快就返回，看看查询语句执行计划。

explain SELECT ps_partkey, sum(ps_availqty) sum_qty, avg(p_retailprice) avg_price FROM mv_wide_part3 WHERE ps_partkey = 1431064 GROUP BY ps_partkey;

1730126229

初步看这个执行计划非常复杂，实际上也比较有特点，就是基表的物化视图日志跟基表和原视图之间的各种连接过滤实现数据增量应用到物化视图上。这就是实时物化视图的优点。

同时业务查询下面 SQL。

set session query_rewrite_enabled = false;
SELECT count(*) FROM part p , partsupp ps WHERE p.p_partkey=ps.ps_partkey AND ps.ps_suppkey=75019;

查看 SQL 执行计划如下。

1730126240

此时，如果开启查询改写变量设置。

1730126251

查询执行计划就变为跟物化视图查询语句相关的执行计划了。当表的数据量非常大且查询语句条件过滤性不高的时候，这种改写后的执行计划性能往往会高出很多。

1730126262

上面 SQL 查询性能对比结果使用实时物化视图的查询改写后，性能提升还是很明显的。

OceanBase 现已支持 365天免费试用，快来尝试最新的物化视图能力吧 >>