基于湖库一体架构，统一管理结构化、半结构化与非结构化等多模态数据，一个系统承载事务处理、实时分析与 AI 工作负载。
划线反馈
嵌套物化视图与级联刷新
更新时间：2026-05-25 09:11
分享
适用版本：V4.3.x、V4.4.x内容类型：TechNote
OceanBase 数据库 V4.3.5.0 开始支持嵌套物化视图，刷新嵌套物化视图时时需要按照层次关系，自底向上依次刷新所有相关的物化视图。
OceanBase 数据库 V4.3.5.3 开始支持嵌套物化视图**级联刷新（级联非一致性刷新），刷新上层物化视图时，自动刷新依赖的下层物化视图。
OceanBase 数据库 V4.3.5.4 开始支持嵌套物化视图级联刷新（级联一致性刷新），刷新上层物化视图时，快照一致性级联刷新依赖的下层物化视图。
嵌套物化视图有如下三种刷新策略：
策略 1- 独立刷新（individual）： 默认行为只刷新当前物化视图，并不刷新依赖的下层物化视图。
策略 2 - 级联非一致性刷新（inconsistent）：自底向上刷新嵌套物化视图依赖的所有物化视图，适用于批量更新的数据源。
策略 3 - 级联一致性刷新（consistent）：快照一致性级联刷新，所有基表的数据位点都是一致的。
使用级联刷新功能时，建议在嵌套物化视图最上层指定定时刷新，中间各层物化视图由上层驱动刷新。对于刷新链路较长的嵌套物化视图来说，为了提高性能，也可以在部分中间节点上指定频次更高的定时刷新任务保证下游物化视图每次增量刷新的开销更小。
本文通过定时级联全量刷新嵌套物化视图和定时级联增量刷新嵌套物化视图逐步演示实例，展现嵌套物化视图级联刷新功能。
详细说明
本文测试用例及所有 SQL 除特别说明外，均适用于 OceanBase 数据库 MySQL 租户 和 Oracle 租户。 更多用法参考官网文档。
MySQL 模式：
创建物化视图。 刷新物化视图。 CREATE MATERIALIZED VIEW。
Oracle 模式：
创建物化视图。 刷新物化视图。 CREATE MATERIALIZED VIEW。
测试用例
创建测试表如下：
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
city_id INT NOT NULL
);
CREATE TABLE city (
city_id INT PRIMARY KEY,
province_id INT NOT NULL
);
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount INT
);
-- 插入客户数据
INSERT INTO customers VALUES (1, 1);
INSERT INTO customers VALUES (2, 2);
INSERT INTO customers VALUES (3, 3);
COMMIT;
-- 插入城市数据
INSERT INTO city VALUES (1, 1);
INSERT INTO city VALUES (2, 2);
INSERT INTO city VALUES (3, 2);
COMMIT;
-- 插入销售数据
INSERT INTO sales VALUES (1, 1, 100);
INSERT INTO sales VALUES (2, 1, 200);
INSERT INTO sales VALUES (3, 2, 500);
COMMIT;
全量刷新嵌套物化视图
创建第一层物化视图
-- 第一层物化视图 客户销售额
CREATE MATERIALIZED VIEW mv_customer_sales (PRIMARY KEY(customer_id))
REFRESH COMPLETE
AS
SELECT
c.customer_id,
count(*) AS count_all,
count(s.amount) AS count_amount,
SUM(s.amount) AS total_sales
FROM
sales s
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY
c.customer_id;
创建后查询结果如下：
MySQL [test]> SELECT * FROM mv_customer_sales;
+-------------+-----------+--------------+-------------+
| customer_id | count_all | count_amount | total_sales |
+-------------+-----------+--------------+-------------+
| 1 | 2 | 2 | 300 |
| 2 | 1 | 1 | 500 |
+-------------+-----------+--------------+-------------+
2 rows in set (0.047 sec)
创建第二层物化视图
-- 第二层物化视图 区域销售汇总
CREATE MATERIALIZED VIEW mv_customer_type_region (PRIMARY KEY(province_id))
REFRESH COMPLETE
AS
SELECT
t.province_id,
count(*) AS count_all,
count(s.total_sales) AS count_total_sales,
SUM(s.total_sales) AS total_sales
FROM
mv_customer_sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN city t ON c.city_id = t.city_id
GROUP BY
t.province_id;
创建后查询结果如下：
MySQL [test]> SELECT * FROM mv_customer_type_region;
+-------------+-----------+-------------------+-------------+
| province_id | count_all | count_total_sales | total_sales |
+-------------+-----------+-------------------+-------------+
| 1 | 1 | 1 | 300 |
| 2 | 1 | 1 | 500 |
+-------------+-----------+-------------------+-------------+
2 rows in set (0.067 sec)
手动刷新嵌套物化视图
插入测试数据：
INSERT INTO sales VALUES (4, 3, 300);
INSERT INTO sales VALUES (5, 3, 1000);
COMMIT;
依次从底层全量刷新物化视图：
CALL dbms_mview.refresh('mv_customer_sales', 'C');
CALL dbms_mview.refresh('mv_customer_type_region', 'C');
执行结果如下：
MySQL [test]> CALL dbms_mview.refresh('mv_customer_sales', 'C');
Query OK, 0 rows affected (1.771 sec)
MySQL [test]> SELECT * FROM mv_customer_sales;
+-------------+-----------+--------------+-------------+
| customer_id | count_all | count_amount | total_sales |
+-------------+-----------+--------------+-------------+
| 1 | 2 | 2 | 300 |
| 2 | 1 | 1 | 500 |
| 3 | 2 | 2 | 1300 |
+-------------+-----------+--------------+-------------+
3 rows in set (0.047 sec)
MySQL [test]> CALL dbms_mview.refresh('mv_customer_type_region', 'C');
Query OK, 0 rows affected (1.951 sec)
MySQL [test]> SELECT * FROM mv_customer_type_region;
+-------------+-----------+-------------------+-------------+
| province_id | count_all | count_total_sales | total_sales |
+-------------+-----------+-------------------+-------------+
| 1 | 1 | 1 | 300 |
| 2 | 2 | 2 | 1800 |
+-------------+-----------+-------------------+-------------+
2 rows in set (0.084 sec)
手动级联刷新嵌套物化视图
在上一步手动刷新嵌套物化视图中，需要依次从底层全量刷新所有物化视图，在嵌套层次较多时很不方便。 下面演示一键级联刷新嵌套物化视图功能。
插入测试数据：
INSERT INTO sales VALUES (6, 1, 600);
COMMIT;
直接全量刷新最上层物化视图，指定为级联刷新。
OceanBase 数据库 MySQL 租户：
CALL dbms_mview.refresh('mv_customer_type_region', 'C', nested => true, nested_refresh_mode => 'inconsistent');
OceanBase 数据库 Oracle 租户：
BEGIN
dbms_mview.refresh('mv_customer_type_region', 'C', nested => true, nested_refresh_mode => 'inconsistent');
END;
/
执行结果如下：
MySQL [test]> CALL dbms_mview.refresh('mv_customer_type_region', 'C', nested => true, nested_refresh_mode => 'inconsistent');
Query OK, 0 rows affected (4.073 sec)
MySQL [test]> SELECT * FROM mv_customer_sales;
+-------------+-----------+--------------+-------------+
| customer_id | count_all | count_amount | total_sales |
+-------------+-----------+--------------+-------------+
| 1 | 3 | 3 | 900 |
| 2 | 1 | 1 | 500 |
| 3 | 2 | 2 | 1300 |
+-------------+-----------+--------------+-------------+
3 rows in set (0.005 sec)
MySQL [test]> SELECT * FROM mv_customer_type_region;
+-------------+-----------+-------------------+-------------+
| province_id | count_all | count_total_sales | total_sales |
+-------------+-----------+-------------------+-------------+
| 1 | 1 | 1 | 900 |
| 2 | 2 | 2 | 1800 |
+-------------+-----------+-------------------+-------------+
2 rows in set (0.059 sec)
定时级联刷新嵌套物化视图
在上一步手动级联刷新嵌套物化视图中，需要手动执行刷新，在需要定期刷新嵌套物化视图时不太方便。 使用级联刷新功能时，建议在嵌套物化视图最上层指定定时刷新，中间各层物化视图由上层驱动刷新。 下面演示通过定义刷新计划，自动定时级联刷新嵌套物化视图功能。
步骤一，设置嵌套物化视图定时刷新。
修改最上层物化视图，增加刷新计划，指定 10 秒刷新一次（实际根据需要设置，如 600 秒刷新一次）。
OceanBase 数据库 MySQL 租户：
ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND;OceanBase 数据库 Oracle 租户：
ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH START WITH sysdate NEXT sysdate + INTERVAL '10' SECOND;步骤二，将定时刷新嵌套物化视图的任务设置为级联刷新。
修改最上层物化视图，将定时刷新任务设置为级联刷新：
ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH INCONSISTENT;数据分两次插入，等待自动刷新结果再次查询，执行结果如下：
MySQL [test]> ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND; Query OK, 0 rows affected (0.212 sec) MySQL [test]> ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH INCONSISTENT; Query OK, 0 rows affected (0.253 sec) MySQL [test]> INSERT INTO sales VALUES (7, 2, 800); Query OK, 1 row affected (0.007 sec) MySQL [test]> COMMIT; Query OK, 0 rows affected (0.001 sec) MySQL [test]> SELECT * FROM mv_customer_sales; +-------------+-----------+--------------+-------------+ | customer_id | count_all | count_amount | total_sales | +-------------+-----------+--------------+-------------+ | 1 | 3 | 3 | 900 | | 2 | 2 | 2 | 1300 | | 3 | 2 | 2 | 1300 | +-------------+-----------+--------------+-------------+ 3 rows in set (0.014 sec) MySQL [test]> SELECT * FROM mv_customer_type_region; +-------------+-----------+-------------------+-------------+ | province_id | count_all | count_total_sales | total_sales | +-------------+-----------+-------------------+-------------+ | 1 | 1 | 1 | 900 | | 2 | 2 | 2 | 2600 | +-------------+-----------+-------------------+-------------+ 2 rows in set (0.045 sec) MySQL [test]> INSERT INTO sales VALUES (8, 2, 2000); Query OK, 1 row affected (0.006 sec) MySQL [test]> COMMIT; Query OK, 0 rows affected (0.001 sec) MySQL [test]> SELECT * FROM mv_customer_sales; +-------------+-----------+--------------+-------------+ | customer_id | count_all | count_amount | total_sales | +-------------+-----------+--------------+-------------+ | 1 | 3 | 3 | 900 | | 2 | 3 | 3 | 3300 | | 3 | 2 | 2 | 1300 | +-------------+-----------+--------------+-------------+ 3 rows in set (0.004 sec) MySQL [test]> SELECT * FROM mv_customer_type_region; +-------------+-----------+-------------------+-------------+ | province_id | count_all | count_total_sales | total_sales | +-------------+-----------+-------------------+-------------+ | 1 | 1 | 1 | 900 | | 2 | 2 | 2 | 4600 | +-------------+-----------+-------------------+-------------+ 2 rows in set (0.079 sec)
增量刷新嵌套物化视图
创建基表物化视图日志
如是全量刷新物化视图不需要创建基表日志。
CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID, SEQUENCE (city_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON city WITH PRIMARY KEY, ROWID, SEQUENCE (province_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON sales WITH PRIMARY KEY, ROWID, SEQUENCE (customer_id, amount) INCLUDING NEW VALUES;
创建第一层物化视图
-- 第一层物化视图 客户销售额
CREATE MATERIALIZED VIEW mv_customer_sales (PRIMARY KEY(customer_id))
REFRESH FAST
AS
SELECT
c.customer_id,
count(*) AS count_all,
count(s.amount) AS count_amount,
SUM(s.amount) AS total_sales
FROM
sales s
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY
c.customer_id;
创建后查询结果如下：
MySQL [test]> SELECT * FROM mv_customer_sales;
+-------------+-----------+--------------+-------------+
| customer_id | count_all | count_amount | total_sales |
+-------------+-----------+--------------+-------------+
| 1 | 2 | 2 | 300 |
| 2 | 1 | 1 | 500 |
+-------------+-----------+--------------+-------------+
2 rows in set (0.047 sec)
创建物化视图 mlog
CREATE MATERIALIZED VIEW LOG ON mv_customer_sales WITH PRIMARY KEY, ROWID, SEQUENCE (total_sales) INCLUDING NEW VALUES;
创建第二层物化视图
-- 第二层物化视图 区域销售汇总
CREATE MATERIALIZED VIEW mv_customer_type_region (PRIMARY KEY(province_id))
REFRESH FAST
AS
SELECT
t.province_id,
count(*) AS count_all,
count(s.total_sales) AS count_total_sales,
SUM(s.total_sales) AS total_sales
FROM
mv_customer_sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN city t ON c.city_id = t.city_id
GROUP BY
t.province_id;
创建后查询结果如下：
MySQL [test]> SELECT * FROM mv_customer_type_region;
+-------------+-----------+-------------------+-------------+
| province_id | count_all | count_total_sales | total_sales |
+-------------+-----------+-------------------+-------------+
| 1 | 1 | 1 | 300 |
| 2 | 1 | 1 | 500 |
+-------------+-----------+-------------------+-------------+
2 rows in set (0.067 sec)
手动刷新嵌套物化视图
插入测试数据：
INSERT INTO sales VALUES (4, 3, 300);
INSERT INTO sales VALUES (5, 3, 1000);
COMMIT;
依次从底层增量刷新物化视图：
CALL dbms_mview.refresh('mv_customer_sales', 'F');
CALL dbms_mview.refresh('mv_customer_type_region', 'F');
执行结果如下：
MySQL [test]> CALL dbms_mview.refresh('mv_customer_sales', 'F');
Query OK, 0 rows affected (0.411 sec)
MySQL [test]> CALL dbms_mview.refresh('mv_customer_type_region', 'F');
Query OK, 0 rows affected (0.517 sec)
MySQL [test]> SELECT * FROM mv_customer_sales;
+-------------+-----------+--------------+-------------+
| customer_id | count_all | count_amount | total_sales |
+-------------+-----------+--------------+-------------+
| 1 | 2 | 2 | 300 |
| 2 | 1 | 1 | 500 |
| 3 | 2 | 2 | 1300 |
+-------------+-----------+--------------+-------------+
3 rows in set (0.001 sec)
MySQL [test]> SELECT * FROM mv_customer_type_region;
+-------------+-----------+-------------------+-------------+
| province_id | count_all | count_total_sales | total_sales |
+-------------+-----------+-------------------+-------------+
| 1 | 1 | 1 | 300 |
| 2 | 2 | 2 | 1800 |
+-------------+-----------+-------------------+-------------+
2 rows in set (0.012 sec)
手动级联刷新嵌套物化视图
在上一步手动刷新嵌套物化视图中，需要依次从底层增量刷新所有物化视图，在嵌套层次较多时很不方便。
下面演示一键级联刷新嵌套物化视图功能。
插入测试数据：
INSERT INTO sales VALUES (6, 1, 600);
COMMIT;
直接增量刷新最上层物化视图，指定为级联刷新。
OceanBase 数据库 MySQL 租户：
CALL dbms_mview.refresh('mv_customer_type_region', 'F', nested => true, nested_refresh_mode => 'inconsistent');
OceanBase 数据库 Oracle 租户：
BEGIN
dbms_mview.refresh('mv_customer_type_region', 'F', nested => true, nested_refresh_mode => 'inconsistent');
END;
/
执行结果如下：
MySQL [test]> CALL dbms_mview.refresh('mv_customer_type_region', 'F', nested => true, nested_refresh_mode => 'inconsistent');
Query OK, 0 rows affected (0.524 sec)
MySQL [test]> SELECT * FROM mv_customer_sales;
+-------------+-----------+--------------+-------------+
| customer_id | count_all | count_amount | total_sales |
+-------------+-----------+--------------+-------------+
| 1 | 3 | 3 | 900 |
| 2 | 1 | 1 | 500 |
| 3 | 2 | 2 | 1300 |
+-------------+-----------+--------------+-------------+
3 rows in set (0.002 sec)
MySQL [test]> SELECT * FROM mv_customer_type_region;
+-------------+-----------+-------------------+-------------+
| province_id | count_all | count_total_sales | total_sales |
+-------------+-----------+-------------------+-------------+
| 1 | 1 | 1 | 900 |
| 2 | 2 | 2 | 1800 |
+-------------+-----------+-------------------+-------------+
2 rows in set (0.012 sec)
定时级联刷新嵌套物化视图
在上一步手动级联刷新嵌套物化视图中，需要手动执行刷新，在需要定期刷新嵌套物化视图时不太方便。 使用级联刷新功能时，建议在嵌套物化视图最上层指定定时刷新，中间各层物化视图由上层驱动刷新。 下面演示通过定义刷新计划，自动定时级联刷新嵌套物化视图功能。
步骤一，设置嵌套物化视图定时刷新。
修改最上层物化视图，增加刷新计划，指定 10 秒刷新一次（实际根据需要设置，如 600 秒刷新一次）。
OceanBase 数据库 MySQL 租户：
ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND;OceanBase 数据库 Oracle 租户：
ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH START WITH sysdate NEXT sysdate + INTERVAL '10' SECOND;步骤二，将定时刷新嵌套物化视图的任务设置为级联刷新。
修改最上层物化视图，将定时刷新任务设置为级联刷新：
ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH INCONSISTENT;数据分两次插入，等待自动刷新结果再次查询，执行结果如下：
MySQL [test]> ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND; Query OK, 0 rows affected (0.209 sec) MySQL [test]> ALTER MATERIALIZED VIEW mv_customer_type_region REFRESH INCONSISTENT; Query OK, 0 rows affected (0.211 sec) MySQL [test]> INSERT INTO sales VALUES (7, 2, 800); Query OK, 1 row affected (0.008 sec) MySQL [test]> COMMIT; Query OK, 0 rows affected (0.001 sec) MySQL [test]> SELECT * FROM mv_customer_sales; +-------------+-----------+--------------+-------------+ | customer_id | count_all | count_amount | total_sales | +-------------+-----------+--------------+-------------+ | 1 | 3 | 3 | 900 | | 2 | 2 | 2 | 1300 | | 3 | 2 | 2 | 1300 | +-------------+-----------+--------------+-------------+ 3 rows in set (0.001 sec) MySQL [test]> SELECT * FROM mv_customer_type_region; +-------------+-----------+-------------------+-------------+ | province_id | count_all | count_total_sales | total_sales | +-------------+-----------+-------------------+-------------+ | 1 | 1 | 1 | 900 | | 2 | 2 | 2 | 2600 | +-------------+-----------+-------------------+-------------+ 2 rows in set (0.011 sec) MySQL [test]> INSERT INTO sales VALUES (8, 2, 2000); Query OK, 1 row affected (0.006 sec) MySQL [test]> COMMIT; Query OK, 0 rows affected (0.001 sec) MySQL [test]> SELECT * FROM mv_customer_sales; +-------------+-----------+--------------+-------------+ | customer_id | count_all | count_amount | total_sales | +-------------+-----------+--------------+-------------+ | 1 | 3 | 3 | 900 | | 2 | 3 | 3 | 3300 | | 3 | 2 | 2 | 1300 | +-------------+-----------+--------------+-------------+ 3 rows in set (0.002 sec) MySQL [test]> SELECT * FROM mv_customer_type_region; +-------------+-----------+-------------------+-------------+ | province_id | count_all | count_total_sales | total_sales | +-------------+-----------+-------------------+-------------+ | 1 | 1 | 1 | 900 | | 2 | 2 | 2 | 4600 | +-------------+-----------+-------------------+-------------+ 2 rows in set (0.011 sec)
适用版本
OceanBase 数据库 V4.3.5 BP3（oceanbase-4.3.5.3-103000102025071821）及之后保本。
