含 LEFT JOIN 增量刷新物化视图使用说明

OceanBase 数据库 V4.3.5 BP3 版本开始支持含 LEFT JOIN 增量刷新物化视图。 本文以 OceanBase 数据库 MySQL 模式为例介绍含 LEFT JOIN 增量刷新物化视图的用法及使用限制，OceanBase 数据库 Oracle 模式用法相同且本文中示例 SQL 可以直接在 OceanBase 数据库 Oracle 模式租户中执行。

详细说明

测试用例

创建测试表如下。

CREATE TABLE test_mv_a (tid INT PRIMARY KEY, aid INT);
INSERT INTO test_mv_a(tid,aid) VALUES (1,1);
INSERT INTO test_mv_a(tid,aid) VALUES (2,2);
INSERT INTO test_mv_a(tid,aid) VALUES (3,1);
COMMIT;

CREATE TABLE test_mv_b (tid INT PRIMARY KEY, bid INT);
INSERT INTO test_mv_b(tid,bid) VALUES (1,11);
INSERT INTO test_mv_b(tid,bid) VALUES (2,22);
COMMIT;

用法

用法一：含 LEFT JOIN 的多表连接增量刷新物化视图

1. 创建基表的 mlog。

   CREATE MATERIALIZED VIEW LOG ON test_mv_a WITH PRIMARY KEY, ROWID, SEQUENCE (aid) INCLUDING NEW VALUES;
   CREATE MATERIALIZED VIEW LOG ON test_mv_b WITH PRIMARY KEY, ROWID, SEQUENCE (bid) INCLUDING NEW VALUES;
   

2. 创建含 LEFT JOIN 的多表连接增量刷新物化视图。

   CREATE MATERIALIZED VIEW mv_test_ab (PRIMARY KEY(tid)) REFRESH FAST AS SELECT a.tid,a.aid,b.tid AS tid_b,b.bid FROM test_mv_a a LEFT JOIN test_mv_b b ON a.tid=b.tid;
   

3. 创建后查询。

   MySQL [test]> SELECT * FROM mv_test_ab;
   

   输出结果如下：

   +-----+------+-------+------+
   | tid | aid  | tid_b | bid  |
   +-----+------+-------+------+
   |   1 |    1 |     1 |   11 |
   |   2 |    2 |     2 |   22 |
   |   3 |    1 |  NULL | NULL |
   +-----+------+-------+------+
   3 rows in set (0.043 sec)
   

用法二：实现含 LEFT JOIN 的多表聚合增量刷新物化视图

由于不直接支持含 LEFT JOIN 的多表聚合增量刷新物化视图，可以分两步实现：

第一步： 创建含 LEFT JOIN 的多表连接增量刷新物化视图，同用法一。 第二步： 根据第一步创建的物化视图进一步创建单表或多表聚合增量刷新物化视图。

1. 创建第一步中的物化视图的 mlog。

   CREATE MATERIALIZED VIEW LOG ON mv_test_ab with (aid, tid_b, bid) INCLUDING NEW VALUES;
   

2. 创建单表或多表聚合增量刷新物化视图（注意聚合列设置别名以方便后续查询）。

   CREATE MATERIALIZED VIEW mv_test_ab_count (PRIMARY KEY(aid)) REFRESH FAST AS SELECT a.aid,count(*) AS count_all,count(a.aid) AS count_aid FROM mv_test_ab a GROUP BY a.aid;
   

3. 创建后查询。

   MySQL [test]> SELECT * FROM mv_test_ab_count;
   

   输出结果如下：

   +-----+-----------+-----------+
   | aid | count_all | count_aid |
   +-----+-----------+-----------+
   |   1 |         2 |         2 |
   |   2 |         1 |         1 |
   +-----+-----------+-----------+
   2 rows in set (0.059 sec)
   

使用限制

使用限制一：不支持创建含 LEFT JOIN 的实时物化视图

MySQL [test]> CREATE MATERIALIZED VIEW mv_test_ab (PRIMARY KEY(tid))
REFRESH FAST ENABLE ON QUERY COMPUTATION
AS
SELECT a.tid,a.aid,b.tid AS tid_b,b.bid FROM test_mv_a a LEFT JOIN test_mv_b b ON a.tid=b.tid;
ERROR 9783 (HY000): cannot ENABLE ON QUERY COMPUTATION for the materialized view mv_test_ab: on query computation is not supported for materialized view with OUTER JOIN

解决方法： 可以创建含 LEFT JOIN 的定时刷新物化视图，具体示例如下。

CREATE MATERIALIZED VIEW mv_test_ab (PRIMARY KEY(tid)) REFRESH FAST START WITH sysdate() NEXT sysdate() + INTERVAL 600 SECOND AS SELECT a.tid,a.aid,b.tid AS tid_b,b.bid FROM test_mv_a a LEFT JOIN test_mv_b b ON a.tid=b.tid;
ERROR 9783 (HY000): cannot ENABLE ON QUERY COMPUTATION for the materialized view mv_test_ab: on query computation is not supported for materialized view with OUTER JOIN

使用限制二：不直接支持含 LEFT JOIN 的多表聚合增量刷新物化视图

不直接支持含 LEFT JOIN 的多表聚合增量刷新物化视图，报错如下。

MySQL [test]> CREATE MATERIALIZED VIEW mv_test_ab (PRIMARY KEY(aid)) REFRESH FAST AS SELECT a.aid,count(*) count_all,count(a.aid) count_aid FROM test_mv_a a LEFT JOIN test_mv_b b ON a.tid=b.tid
GROUP BY a.aid;
ERROR 9760 (HY000): cannot fast refresh materialized view mv_test_ab: OUTER JOIN is not supported for materialized view with aggregate

解决方法： 参考上述用法二实现，具体示例如下。

CREATE MATERIALIZED VIEW mv_test_ab (PRIMARY KEY(tid)) REFRESH FAST AS SELECT a.tid,a.aid,b.tid AS tid_b,b.bid FROM test_mv_a a LEFT JOIN test_mv_b b ON a.tid=b.tid;

CREATE MATERIALIZED VIEW LOG ON mv_test_ab with (aid, tid_b, bid) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_test_ab_count (PRIMARY KEY(aid)) REFRESH FAST AS SELECT a.aid,count(*) AS count_all,count(a.aid) AS count_aid FROM mv_test_ab a GROUP BY a.aid;

适用版本

OceanBase 数据库 V4.3.5 BP3（oceanbase-4.3.5.3-103000062025071510）版本。