---
title: 含 LEFT JOIN 增量刷新物化视图使用说明-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于含 LEFT JOIN 增量刷新物化视图使用说明相关的常见问题和使用技巧，帮助您快速解决含 LEFT JOIN 增量刷新物化视图使用说明的难题。
image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

划线反馈

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

更新时间：2026-05-25 09:11

适用版本： V4.3.x 内容类型：TechNote  

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

## 详细说明

### 测试用例

创建测试表如下。

```shell
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。

   ```shell
   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 的多表连接增量刷新物化视图。

   ```shell
   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. 创建后查询。

   ```shell
   MySQL [test]> SELECT * FROM mv_test_ab;

   ```

   输出结果如下：

   ```shell
   +-----+------+-------+------+
   | 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。

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

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

   ```shell
   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;

   ```shell
   MySQL [test]> SELECT * FROM mv_test_ab_count;

   ```

   输出结果如下：

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

   ```

### 使用限制

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

```shell
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 的定时刷新物化视图，具体示例如下。

```shell
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 的**多表聚合**增量刷新物化视图，报错如下。

```shell
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 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）版本。

Previous

[dba_ob_tenant_jobs 视图 job_status 字段显示 skip_checking_ls_status](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003891638)

Next

[含聚合函数的增量刷新物化视图使用说明](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003523125) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线