--- title: "创建物化视图 - OceanBase 数据库 V4.3.2 | OceanBase 文档中心" description: 创建物化视图 本文将向您介绍如何使用 SQL 语句创建物化视图。 说明 OceanBase 数据库暂时不支持直接修改物化视图的属性，如更新时间、刷新方法等。在这种情况下，可以考虑删除并重新创建物化视图以达到修改物化视图的目的。 权限要求 创建物化视图需要有 CREATE TABLE 权限。更多有关 OceanBase… --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.3.2 # 创建物化视图 更新时间：2025-01-17 14:56:51 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.3.2/zh-CN/700.reference/300.database-object-management/100.manage-object-of-mysql-mode/600.manage-views-of-mysql-mode/200.manage-materialized-views-of-mysql-mode/100.materialized-views-of-mysql-mode/200.create-materialized-views-of-mysql-mode.md) 本文将向您介绍如何使用 SQL 语句创建物化视图。 #### 说明 OceanBase 数据库暂时不支持直接修改物化视图的属性，如更新时间、刷新方法等。在这种情况下，可以考虑删除并重新创建物化视图以达到修改物化视图的目的。 ## 权限要求 创建物化视图需要有 `CREATE TABLE` 权限。更多有关 OceanBase 数据库权限的详细介绍，请参见 [MySQL 模式下的权限分类](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001052880)。 ## 语法 创建物化视图的 SQL 语句格式如下： ```sql CREATE MATERIALIZED VIEW view_name [([column_list] [PRIMARY KEY(column_list)])] [table_option_list] [partition_option] [refresh_clause] [query_rewrite_clause] [on_query_computation_clause] AS view_select_stmt; ``` 有关创建物化视图语法的详细参数说明信息，请参见 [CREATE MATERIALIZED VIEW](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053503)。 ### 创建测试表 1. 创建表 `tbl1`。 ```sql CREATE TABLE tbl1 (id INT PRIMARY KEY, name VARCHAR(20), age INT); ``` 2. 创建表 `tbl2`。 ```sql CREATE TABLE tbl2 (id INT PRIMARY KEY, tbl1_id INT NOT NULL, notes VARCHAR(50), CONSTRAINT fk_tbl2 FOREIGN KEY (tbl1_id) REFERENCES tbl1 (id)); ``` 3. 创建表 `tbl3`。 ```sql CREATE TABLE tbl3 (col1 INT PRIMARY KEY, col2 INT, col3 INT, col4 INT); ``` ## 创建物化视图时添加主键 #### 注意 为物化视图指定主键后，维护/更新物化视图数据时，如果数据不满足主键约束，将导致视图维护失败。 **示例如下：** 创建名为 `mv0_tbl1` 的物化视图，并指定主键。 ```sql CREATE MATERIALIZED VIEW mv0_tbl1(v_id, v_name, PRIMARY KEY(v_id)) AS SELECT id, name FROM tbl1 WHERE age >= 20; ``` ## 物化视图表选项和分区选项 在创建物化视图时，可以设置表选项，并根据数据特征和访问模式来设计和配置适合的分区选项，以提高查询性能和管理效率。 有关表选项和分区选项的详细参数说明信息，请参见 [CREATE TABLE](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053522)。 **示例如下：** 创建名为 `mv_tbl1` 的物化视图。指定物化视图的并行度为 `5`，将物化视图按照 `id` 列进行 Hash 分区，并分为 `8` 个分区；将 `tbl1` 表中满足条件 `age >= 20` 的记录作为基表进行查询，并将查询结果作为物化视图的数据。 ```sql CREATE MATERIALIZED VIEW mv_tbl1(id, name) PARALLEL 5 PARTITION BY HASH(id) PARTITIONS 8 AS SELECT id, name FROM tbl1 WHERE age >= 20; ``` ## 物化视图添加索引 #### 说明 在创建物化视图的语句中，不能直接创建索引。您可以使用 [CREATE INDEX](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053496) 语句或者 [ALTER TABLE](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053474) 语句为物化视图创建索引。 **示例如下：** - 在物化视图 `mv_tbl1` 的 `id` 列上创建名为 `idx1_mv_tbl1` 的索引。 ```sql CREATE INDEX idx1_mv_tbl1 ON mv_tbl1(id); ``` - 在物化视图 `mv_tbl1` 的 `name` 列上创建名为 `idx2_mv_tbl1` 的索引。 ```sql ALTER TABLE mv_tbl1 ADD INDEX idx2_mv_tbl1(name); ``` ## 刷新模式（刷新时机） OceanBase 数据库目前仅支持 `ON DEMAND` 刷新模式，即物化视图在您需要的时候进行刷新。 您还可以通过调用 `DBMS_MVIEW` 包来手动刷新物化视图，也可以在创建物化视图时设置 `START WITH ... NEXT ...` 定时刷新。 更多有关 `DBMS_MVIEW` 的详细信息，请参见 [DBMS_MVIEW 概述](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053769)。 ## 刷新方法 OceanBase 数据库物化视图的刷新方法有以下三种： - 全量刷新（`COMPLETE`）：重新计算整个物化视图的数据，确保视图中的数据与源表完全一致。 - 增量刷新（`FAST`）：只刷新与源表变化相关的数据，避免对整个视图进行完全计算。 #### 注意 - 由于 `REFRESH FAST` 方法利用物化视图日志中的记录信息来确定需要增量刷新的内容，因此在使用增量刷新刷新物化视图时，需要在创建物化视图之前就创建基表的物化视图日志（mlog）。 - 增量刷新物化视图中使用的列都必须在 mlog 里。有关创建物化视图日志的信息，请参见 [创建物化视图日志](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053749)。 - 混合刷新（`FORCE`）：默认选项，首先尝试增量刷新，如果增量刷新失败，则执行全量刷新。 更多物化视图刷新的信息，请参见 [刷新物化视图](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053756)。 ### 全量刷新 使用 `REFRESH COMPLETE` 子句设置物化视图的刷新方法为全量刷新。 **示例如下：** 创建名为 `mv1_tbl1` 的物化视图。指定物化视图的刷新方法为全量刷新，并且可以根据需要手动触发刷新操作；指定从 `tbl1` 表中选择满足 `age` 大于等于 `20` 的 `id` 和 `name` 列作为物化视图的数据源。 ```sql CREATE MATERIALIZED VIEW mv1_tbl1(id, name) REFRESH COMPLETE ON DEMAND AS SELECT id, name FROM tbl1 WHERE age >= 20; ``` ### 增量刷新 使用 `REFRESH FAST` 子句设置物化视图的刷新方法为增量刷新。 **示例如下：** 1. 在 `tbl3` 表上创建物化视图日志。指定物化视图日志的选项为 `SEQUENCE`，用于指示使用序列号来标识变化的数据，列部分指定了要记录的列，其中包括了 `col2` 和 `col3`。 ```sql CREATE MATERIALIZED VIEW LOG ON tbl3 WITH SEQUENCE (col2, col3) INCLUDING NEW VALUES; ``` 2. 创建名为 `mv_tbl3` 的物化视图。指定物化视图的刷新方法为增量刷新，并且可以根据需要手动触发刷新操作；在查询部分，指定了从 `tbl3` 表中按照 `col2` 列进行分组，并计算每个分组中的记录数（`cnt`）、非空 `col3` 列的记录数（`cnt_col3`）和 `col3` 列的总和（`sum_col3`）作为物化视图的结果。 ```sql CREATE MATERIALIZED VIEW mv_tbl3 REFRESH FAST ON DEMAND AS SELECT col2, COUNT(*) cnt, COUNT(col3) cnt_col3, SUM(col3) sum_col3 FROM tbl3 GROUP BY col2; ``` ### 混合刷新（默认选项） 使用 `REFRESH FORCE` 子句设置物化视图的刷新方法为混合刷新。 **示例如下：** 创建名为 `mv2_tbl1` 的物化视图。指定物化视图的刷新方法为混合刷新，并且可以根据需要手动触发刷新操作；指定从 `tbl1` 表中选择满足 `age` 大于等于 `20` 的 `id` 和 `name` 列作为物化视图的数据源。 ```sql CREATE MATERIALIZED VIEW mv2_tbl1(id, name) REFRESH FORCE ON DEMAND AS SELECT id, name FROM tbl1 WHERE age >= 20; ``` ### 永不刷新 使用 `NEVER REFRESH` 子句设置物化视图不需要刷新。即表示物化视图只在创建时进行刷新，并在创建后不允许再次刷新。 **示例如下：** 创建名为 `mv3_tbl1` 的物化视图。指定物化视图的刷新方法为永不刷新；指定从 `tbl1` 表中选择满足 `age` 大于等于 `20` 的 `id` 和 `name` 列作为物化视图的数据源。 ```sql CREATE MATERIALIZED VIEW mv3_tbl1(id, name) NEVER REFRESH AS SELECT id, name FROM tbl1 WHERE age >= 20; ``` ## 自动刷新物化视图 在创建物化视图时，可以通过指定 `START WITH datetime_expr` 和 `NEXT datetime_expr` 子句来为物化视图创建一个后台自动刷新任务。 #### 注意 如果使用了 `NEXT` 子句，刷新计划的时间表达式必须设定在未来的时间点，否则会引发错误。 **示例如下：** 创建名为 `mv_tbl1_tbl2` 的物化视图。指定物化视图的刷新方法为全量刷新；指定物化视图的刷新计划中初始刷新时间为当前日期，之后每隔 `1` 天刷新一次物化视图；在物化视图的查询部分，指定从 `tbl1` 表和 `tbl2` 表中选择 `id`、`name` 和 `notes` 列作为物化视图的数据源，并使用 `t1.id = t2.tbl1_id` 的条件进行连接。 ```sql CREATE MATERIALIZED VIEW mv_tbl1_tbl2 REFRESH COMPLETE START WITH sysdate() NEXT sysdate() + interval 1 day AS SELECT t1.id, t1.name, t2.notes FROM tbl1 t1, tbl2 t2 WHERE t1.id = t2.tbl1_id; ``` ## 创建实时物化视图 创建物化视图时，指定 `ENABLE ON QUERY COMPUTATION` 创建实时物化视图。 实时物化视图是一种支持获取实时数据的数据库对象，它通过使用 mlog 的机制来捕获和处理底层基表的更改，确保物化视图中的数据能及时反映最新状态。在执行查询时，实时物化视图会通过在线计算，即时集成这些更改，以向用户展示更新后的数据。这样，即便物化视图没有物理地存储最新的数据，用户仍可以得到实时更新的查询结果。 ### 创建实时物化视图注意事项 只有特定类型的物化视图支持指定为实时物化视图，对于不满足条件的物化视图指定实时物化视图时，会导致报错。实时物化视图的要求与增量刷新的物化视图要求是相同的。详细信息，参见 [刷新物化视图](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053756) 中 **增量刷新基本要求** 内容。 **示例如下：** 1. 创建测试表 `tbl4`。 ```sql CREATE TABLE tbl4(col1 INT, col2 INT, col3 INT); ``` 2. 创建物化视图日志。 ```sql CREATE MATERIALIZED VIEW LOG ON tbl4 WITH PRIMARY KEY, ROWID, SEQUENCE (col1, col2, col3) INCLUDING NEW VALUES; ``` 3. 创建实时物化视图 `mv1_tbl4`。 ```sql CREATE MATERIALIZED VIEW mv1_tbl4 ENABLE ON QUERY COMPUTATION AS SELECT col1, count(*) AS cnt FROM tbl4 GROUP BY col1; ``` 4. 创建实时物化视图后，可以通过视图 [DBA_MVIEWS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001052066) 查看物化视图的定位是否为实时物化视图。 ```sql SELECT MVIEW_NAME, ON_QUERY_COMPUTATION FROM oceanbase.DBA_MVIEWS WHERE MVIEW_NAME = 'mv1_tbl4'; ``` 返回结果如下： ```shell +------------+----------------------+ | MVIEW_NAME | ON_QUERY_COMPUTATION | +------------+----------------------+ | mv1_tbl4 | Y | +------------+----------------------+ 1 row in set ``` 5. 查看实时物化视图的执行计划。 ```sql EXPLAIN BASIC SELECT * FROM mv1_tbl4; ``` 从下面的执行计划中可以看到，执行期间会同时从物化视图和视图依赖基表的 mlog 读取数据，并对这两部分数据进行计算整合，最终获取实时的物化视图数据。 返回结果如下： ```shell +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================= | | |ID|OPERATOR |NAME | | | --------------------------------------------- | | |0 |HASH FULL OUTER JOIN | | | | |1 |├─SUBPLAN SCAN |DLT_BASIC_MV$$| | | |2 |│ └─HASH GROUP BY | | | | |3 |│ └─SUBPLAN SCAN |DLT_T$$ | | | |4 |│ └─WINDOW FUNCTION | | | | |5 |│ └─TABLE FULL SCAN|mlog$_tbl4 | | | |6 |└─TABLE FULL SCAN |mv1_tbl4 | | | ============================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([cast(nvl(cast(mv1_tbl4.col1, BIGINT(11, 0)), cast(DLT_BASIC_MV$$.col1, BIGINT(11, 0))), INT(11, 0))], [cast(CASE WHEN mv1_tbl4.cnt IS NULL | | THEN DLT_BASIC_MV$$.cnt WHEN DLT_BASIC_MV$$.cnt IS NULL THEN cast(mv1_tbl4.cnt, DECIMAL_INT(43, 0)) ELSE cast(mv1_tbl4.cnt, DECIMAL_INT(43, 0)) + DLT_BASIC_MV$$.cnt | | END, BIGINT(20, 0))]), filter([CASE WHEN mv1_tbl4.cnt IS NULL THEN DLT_BASIC_MV$$.cnt WHEN DLT_BASIC_MV$$.cnt IS NULL THEN cast(mv1_tbl4.cnt, DECIMAL_INT(43, | | 0)) ELSE cast(mv1_tbl4.cnt, DECIMAL_INT(43, 0)) + DLT_BASIC_MV$$.cnt END > cast(0, DECIMAL_INT(43, 0))]), rowset=16 | | equal_conds([mv1_tbl4.col1 <=> DLT_BASIC_MV$$.col1]), other_conds(nil) | | 1 - output([DLT_BASIC_MV$$.col1], [DLT_BASIC_MV$$.cnt]), filter(nil), rowset=16 | | access([DLT_BASIC_MV$$.col1], [DLT_BASIC_MV$$.cnt]) | | 2 - output([DLT_T$$.col1], [T_FUN_SUM(CASE WHEN DLT_T$$.OLD_NEW$$ = 'N' THEN 1 ELSE -1 END)]), filter(nil), rowset=16 | | group([DLT_T$$.col1]), agg_func([T_FUN_SUM(CASE WHEN DLT_T$$.OLD_NEW$$ = 'N' THEN 1 ELSE -1 END)]) | | 3 - output([DLT_T$$.OLD_NEW$$], [DLT_T$$.col1]), filter([DLT_T$$.OLD_NEW$$ = 'N' AND DLT_T$$.SEQUENCE$$ = DLT_T$$.MAXSEQ$$ OR DLT_T$$.OLD_NEW$$ = 'O' | | AND DLT_T$$.SEQUENCE$$ = DLT_T$$.MINSEQ$$]), rowset=16 | | access([DLT_T$$.OLD_NEW$$], [DLT_T$$.SEQUENCE$$], [DLT_T$$.MAXSEQ$$], [DLT_T$$.MINSEQ$$], [DLT_T$$.col1]) | | 4 - output([mlog$_tbl4.OLD_NEW$$], [mlog$_tbl4.SEQUENCE$$], [T_FUN_MAX(mlog$_tbl4.SEQUENCE$$)], [T_FUN_MIN(mlog$_tbl4.SEQUENCE$$)], [mlog$_tbl4.col1]), filter(nil), rowset=16 | | win_expr(T_FUN_MAX(mlog$_tbl4.SEQUENCE$$)), partition_by([mlog$_tbl4.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED | | FOLLOWING) | | win_expr(T_FUN_MIN(mlog$_tbl4.SEQUENCE$$)), partition_by([mlog$_tbl4.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED | | FOLLOWING) | | 5 - output([mlog$_tbl4.M_ROW$$], [mlog$_tbl4.SEQUENCE$$], [mlog$_tbl4.OLD_NEW$$], [mlog$_tbl4.col1], [ORA_ROWSCN]), filter([ORA_ROWSCN > last_refresh_scn(500082)]), rowset=16 | | access([mlog$_tbl4.M_ROW$$], [mlog$_tbl4.SEQUENCE$$], [mlog$_tbl4.OLD_NEW$$], [mlog$_tbl4.col1], [ORA_ROWSCN]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([mlog$_tbl4.M_ROW$$], [mlog$_tbl4.SEQUENCE$$]), range(MIN,MIN ; MAX,MAX)always true | | 6 - output([mv1_tbl4.col1], [mv1_tbl4.cnt]), filter(nil), rowset=16 | | access([mv1_tbl4.col1], [mv1_tbl4.cnt]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([mv1_tbl4.__pk_increment]), range(MIN ; MAX)always true | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 38 rows in set ``` ## 物化视图改写 创建物化视图时，指定 `ENABLE QUERY REWRITE` 开启当前物化视图的自动改写。更多物化视图改写和改写控制的信息，参见 [物化视图查询改写](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053753)。 #### 注意 本功能需要物化视图仅包含 `SELECT JOIN` 与 `WHERE` 子句，即 SPJ 查询。对于不满足条件的物化视图，不会报错，但不会被用于改写。 **示例如下：** 1. 创建物化视图 `mv4_tbl1`，并开启自动改写。 ```sql CREATE MATERIALIZED VIEW mv4_tbl1 ENABLE QUERY REWRITE AS SELECT * FROM tbl1; ``` 2. 创建物化视图后，可以通过视图 [DBA_MVIEWS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001052066) 查看物化视图是否开启自动改写。 ```sql SELECT MVIEW_NAME, REWRITE_ENABLED FROM oceanbase.DBA_MVIEWS WHERE MVIEW_NAME = 'mv4_tbl1'; ``` 返回结果如下： ```shell +------------+-----------------+ | MVIEW_NAME | REWRITE_ENABLED | +------------+-----------------+ | mv4_tbl1 | Y | +------------+-----------------+ 1 row in set ``` ## 相关文档 - [CREATE MATERIALIZED VIEW](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053503) - [创建物化视图日志](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053749) - [查询物化视图](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053757) - [删除物化视图](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053754) - [刷新物化视图](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053756) 上一篇 下一篇 ![有帮助](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) 咨询热线