创建物化视图

本文将向您介绍如何使用 SQL 语句创建物化视图。

权限要求

创建物化视图需要有 CREATE TABLE 权限。更多有关 OceanBase 数据库权限的详细介绍，请参见 MySQL 模式下的权限分类。

语法

创建物化视图的 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。

创建测试表

1. 创建表 tbl1。

   CREATE TABLE tbl1 (id INT PRIMARY KEY, name VARCHAR(20), age INT);
   

2. 创建表 tbl2。

   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。

   CREATE TABLE tbl3 (col1 INT PRIMARY KEY, col2 INT, col3 INT, col4 INT);
   

创建物化视图时添加主键

示例如下：

创建名为 mv0_tbl1 的物化视图，并指定主键。

CREATE MATERIALIZED VIEW mv0_tbl1(v_id, v_name, PRIMARY KEY(v_id))
  AS SELECT id, name
     FROM tbl1
     WHERE age >= 20;

物化视图表选项和分区选项

在创建物化视图时，可以设置表选项，并根据数据特征和访问模式来设计和配置适合的分区选项，以提高查询性能和管理效率。

有关表选项和分区选项的详细参数说明信息，请参见 CREATE TABLE。

示例如下：

创建名为 mv_tbl1 的物化视图。指定物化视图的并行度为 5，将物化视图按照 id 列进行 Hash 分区，并分为 8 个分区；将 tbl1 表中满足条件 age >= 20 的记录作为基表进行查询，并将查询结果作为物化视图的数据。

CREATE MATERIALIZED VIEW mv_tbl1(id, name)
  PARALLEL 5
  PARTITION BY HASH(id) PARTITIONS 8
  AS SELECT id, name
     FROM tbl1
     WHERE age >= 20;

物化视图添加索引

示例如下：

• 在物化视图 mv_tbl1 的 id 列上创建名为 idx1_mv_tbl1 的索引。

  CREATE INDEX idx1_mv_tbl1 ON mv_tbl1(id); 
  

• 在物化视图 mv_tbl1 的 name 列上创建名为 idx2_mv_tbl1 的索引。

  ALTER TABLE mv_tbl1 ADD INDEX idx2_mv_tbl1(name);
  

刷新模式（刷新时机）

OceanBase 数据库目前仅支持 ON DEMAND 刷新模式，即物化视图在您需要的时候进行刷新。

您还可以通过调用 DBMS_MVIEW 包来手动刷新物化视图，也可以在创建物化视图时设置 START WITH ... NEXT ... 定时刷新。

更多有关 DBMS_MVIEW 的详细信息，请参见 DBMS_MVIEW 概述。

刷新方法

OceanBase 数据库物化视图的刷新方法有以下三种：

• 全量刷新（COMPLETE）：重新计算整个物化视图的数据，确保视图中的数据与源表完全一致。

• 增量刷新（FAST）：只刷新与源表变化相关的数据，避免对整个视图进行完全计算。

  注意

  • 由于 REFRESH FAST 方法利用物化视图日志中的记录信息来确定需要增量刷新的内容，因此在使用增量刷新刷新物化视图时，需要在创建物化视图之前就创建基表的物化视图日志（mlog）。
  • 增量刷新物化视图中使用的列都必须在 mlog 里。

  有关创建物化视图日志的信息，请参见 创建物化视图日志。

• 混合刷新（FORCE）：默认选项，首先尝试增量刷新，如果增量刷新失败，则执行全量刷新。

更多物化视图刷新的信息，请参见 刷新物化视图。

全量刷新

使用 REFRESH COMPLETE 子句设置物化视图的刷新方法为全量刷新。

示例如下：

创建名为 mv1_tbl1 的物化视图。指定物化视图的刷新方法为全量刷新，并且可以根据需要手动触发刷新操作；指定从 tbl1 表中选择满足 age 大于等于 20 的 id 和 name 列作为物化视图的数据源。

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。

   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）作为物化视图的结果。

   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 列作为物化视图的数据源。

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 列作为物化视图的数据源。

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 子句来为物化视图创建一个后台自动刷新任务。

示例如下：

创建名为 mv_tbl1_tbl2 的物化视图。指定物化视图的刷新方法为全量刷新；指定物化视图的刷新计划中初始刷新时间为当前日期，之后每隔 1 天刷新一次物化视图；在物化视图的查询部分，指定从 tbl1 表和 tbl2 表中选择 id、name 和 notes 列作为物化视图的数据源，并使用 t1.id = t2.tbl1_id 的条件进行连接。

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 的机制来捕获和处理底层基表的更改，确保物化视图中的数据能及时反映最新状态。在执行查询时，实时物化视图会通过在线计算，即时集成这些更改，以向用户展示更新后的数据。这样，即便物化视图没有物理地存储最新的数据，用户仍可以得到实时更新的查询结果。

创建实时物化视图注意事项

只有特定类型的物化视图支持指定为实时物化视图，对于不满足条件的物化视图指定实时物化视图时，会导致报错。实时物化视图的要求与增量刷新的物化视图要求是相同的。详细信息，参见 刷新物化视图 中 增量刷新基本要求 内容。

示例如下：

1. 创建测试表 tbl4。

   CREATE TABLE tbl4(col1 INT, col2 INT, col3 INT);
   

2. 创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON tbl4 WITH PRIMARY KEY, ROWID, SEQUENCE (col1, col2, col3) INCLUDING NEW VALUES;
   

3. 创建实时物化视图 mv1_tbl4。

   CREATE MATERIALIZED VIEW mv1_tbl4
     ENABLE ON QUERY COMPUTATION
     AS SELECT col1, count(*) AS cnt
         FROM tbl4
         GROUP BY col1;
   

4. 创建实时物化视图后，可以通过视图 DBA_MVIEWS 查看物化视图的定位是否为实时物化视图。

   SELECT MVIEW_NAME, ON_QUERY_COMPUTATION FROM oceanbase.DBA_MVIEWS WHERE MVIEW_NAME = 'mv1_tbl4';
   

   返回结果如下：

   +------------+----------------------+
   | MVIEW_NAME | ON_QUERY_COMPUTATION |
   +------------+----------------------+
   | mv1_tbl4   | Y                    |
   +------------+----------------------+
   1 row in set
   

5. 查看实时物化视图的执行计划。

   EXPLAIN BASIC SELECT * FROM mv1_tbl4;
   

   从下面的执行计划中可以看到，执行期间会同时从物化视图和视图依赖基表的 mlog 读取数据，并对这两部分数据进行计算整合，最终获取实时的物化视图数据。

   返回结果如下：

   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | 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 开启当前物化视图的自动改写。更多物化视图改写和改写控制的信息，参见 物化视图查询改写。

示例如下：

1. 创建物化视图 mv4_tbl1，并开启自动改写。

   CREATE MATERIALIZED VIEW mv4_tbl1
       ENABLE QUERY REWRITE
       AS SELECT *
           FROM tbl1;
   

2. 创建物化视图后，可以通过视图 DBA_MVIEWS 查看物化视图是否开启自动改写。

   SELECT MVIEW_NAME, REWRITE_ENABLED FROM oceanbase.DBA_MVIEWS WHERE MVIEW_NAME = 'mv4_tbl1';
   

   返回结果如下：

   +------------+-----------------+
   | MVIEW_NAME | REWRITE_ENABLED |
   +------------+-----------------+
   | mv4_tbl1   | Y               |
   +------------+-----------------+
   1 row in set
   

相关文档

• CREATE MATERIALIZED VIEW
• 创建物化视图日志
• 查询物化视图
• 删除物化视图
• 刷新物化视图