物化视图日志

物化视图日志（Materialized View Log，mlog）用于记录基表的增量更新数据，以支持物化视图的快速刷新功能。mlog 是一个记录表，追踪基表的变化，并将这些变化应用于相应的物化视图，实现快速刷新。

OceanBase 数据库支持手动管理物化视图日志和自动管理物化视图日志。

使用限制

• 只能在普通表和物化视图上创建物化视图日志。
• 一张基表只与一个物化视图日志做绑定。
• 在创建物化视图日志时，如果基表正在运行事务，则创建操作将被阻塞，直到该事务结束。
• 物化视图日志支持 LOB 类型的列，但是仅支持 LOB 数据内联存储。有关 LOB 类型的介绍信息，参见 LOB 类型。
• 物化视图日志暂不支持以下四种类型的数据：JSON、XML、空间数据和 UDT。
• 物化视图日志暂不支持生成列（包括虚拟列和非虚拟列）。
• 物化视图日志暂不支持指定分区，其分区和基表的分区是绑定关系。
• 物化视图日志的名称长度上限与普通表相同，不能超过 64 个字符。由于物化视图日志的名称会加上 mlog$_ 前缀，因此创建物化视图日志的基表名称不能超过 58 个字符。
• 物化视图日志不支持表级恢复。
• 物化视图日志在单独删除时不会进入回收站。
• 物化视图日志在创建后不支持 ALTER 操作。
• 物化视图日志上不支持建立索引。
• 物化视图日志不支持进行 DML 操作，会报错。

物化视图日志 Schema 定义

一个表只能有一个物化视图日志，其 Schema 名称为 mlog$_table，其中 table 为基表的名称。

物化视图日志的 Schema 定义如下：

操作已有的物化视图日志

• 您可以直接查询物化视图日志所在的 Schema 的结构和其中的数据。
• 您可以通过 DBMS_MVIEW.PURGE_LOG(table_name) 来对某个基表的物化视图日志执行 PURGE 操作。
• 如果物化视图日志的大小增长到超过可用的磁盘容量，将会报错。此时，您必须先删除该物化视图日志并重新创建，才能继续使用它。

基表操作对物化视图日志的影响

基表 DML 操作

物化视图日志表的定义就是负责记录对基表 DML 操作，因此对基表进行 INSERT、DELETE 和 UPDATE 操作最终都会记录到物化视图日志中，具体如下：

• 对基表进行 INSERT 操作，则插入的每行数据也会生成一条记录插入在物化视图日志中；该记录的 dmltype$$ 列值为 I，old_new$$ 列值为 N。
• 对基表进行 DELETE 操作，则删除的每行数据也会生成一条记录插入在物化视图日志中；该记录的 dmltype$$ 列值为 D，old_new$$ 列值为 O。
• 对基表进行 UPDATE 操作，则修改的每行数据也会生成两条记录插入在物化视图日志中；第一行记录的是被 UPDATE 行的旧值，dmltype$$ 列值为 U，old_new$$ 列值为 O；第二行记录的是 UPDATE 后的新值，dmltype$$ 列值为 U，old_new$$ 列值为 N。

基表 DDL 操作

OceanBase 数据库物化视图日志可以直接删除基表，无需先删除相应的物化视图日志再删除基表。

更多有关基表支持的 DDL 操作信息，请参见 Online DDL 和 Offline DDL 操作。

自动管理物化视图日志

OceanBase 数据库支持物化视图日志（Materialized View Log，mlog）自动管理功能，该功能包含以下方面：

• 在创建物化视图时，分析物化视图对于基表的依赖，自动创建所需要的 mlog。
• 删除物化视图（MV）时，MV 依赖的 mlog 如果不被其他 MV 依赖，则 mlog 会被删除。

OceanBase 数据库默认开启物化视图日志自动管理功能，在创建增量刷新物化视图或实时物化视图时，OceanBase 数据库会自动创建 mlog 或自动更新 mlog 定义。

使用限制

创建物化视图时，只有显式声明为增量刷新物化视图（即指定 REFRESH FAST）或实时物化视图（即指定 ENABLE ON QUERY COMPUTATION），才会自动创建 mlog 或自动更新 mlog 定义。

注意事项

• 如果物化视图有新增依赖列，系统将采用 Online 在尾部加列的方式更新 mlog。

相关配置

OceanBase 数据库提供下面配置项以控制 mlog 自动化的行为：

• enable_mlog_auto_maintenance：用于控制是否启用物化视图日志自动管理功能。

示例

开启 mlog 自动化管理。

obclient> ALTER SYSTEM SET enable_mlog_auto_maintenance = True;

示例一：自动创建 mlog

1. 创建表 test_tbl1。

   obclient> CREATE TABLE test_tbl1(col1 INT, col2 INT, col3 INT);
   

2. 直接在表 test_tbl1 上创建增量刷新物化视图 mv_test_tbl1。OceanBase 数据库会自动为 test_tbl1 表在所需的 col2 列上创建 mlog。

   obclient> CREATE MATERIALIZED VIEW mv_test_tbl1
       REFRESH FAST
       AS SELECT
              col2,
              count(*) cnt
          FROM test_tbl1
          GROUP BY col2;
   

3. 查看表 test_tbl1 上物化视图日志的信息。

   obclient> DESC mlog$_test_tbl1;
   

   返回结果如下：

   +------------+---------------------+------+------+---------+-------+
   | FIELD      | TYPE                | NULL | KEY  | DEFAULT | EXTRA |
   +------------+---------------------+------+------+---------+-------+
   | COL2       | NUMBER(38)          | YES  | NULL | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)          | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 )        | NO   | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 )        | NO   | NULL | NULL    | NULL  |
   | M_ROW$$    | BIGINT(20) UNSIGNED | NO   | PRI  | NULL    | NULL  |
   +------------+---------------------+------+------+---------+-------+
   5 rows in set
   

示例二：自动更新 mlog 定义

1. 创建表 test_tbl2。

   obclient> CREATE TABLE test_tbl2(col1 INT, col2 INT, col3 INT);
   

2. 直接在表 test_tbl2 上创建增量刷新物化视图 mv1_test_tbl2。OceanBase 数据库会自动为 test_tbl2 表在所需的 col2 列上创建 mlog。

   obclient> CREATE MATERIALIZED VIEW mv1_test_tbl2
       REFRESH FAST
       AS SELECT
              col2,
              count(*) cnt
          FROM test_tbl2
          GROUP BY col2;
   

3. 查看表 test_tbl2 上物化视图日志的信息。

   obclient> DESC mlog$_test_tbl2;
   

   返回结果如下：

   +------------+---------------------+------+------+---------+-------+
   | FIELD      | TYPE                | NULL | KEY  | DEFAULT | EXTRA |
   +------------+---------------------+------+------+---------+-------+
   | COL2       | NUMBER(38)          | YES  | NULL | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)          | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 )        | NO   | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 )        | NO   | NULL | NULL    | NULL  |
   | M_ROW$$    | BIGINT(20) UNSIGNED | NO   | PRI  | NULL    | NULL  |
   +------------+---------------------+------+------+---------+-------+
   5 rows in set
   

4. 直接在表 test_tbl2 上创建增量刷新物化视图 mv2_test_tbl2。OceanBase 数据库检测到当前 mlog 表中只包含了 col2 列，因此会修改现有 mlog 定义，在 mlog 表中增加 col3 列。

   obclient> CREATE MATERIALIZED VIEW mv2_test_tbl2
       REFRESH FAST
       AS SELECT
           col3,
           count(*) cnt
       FROM test_tbl2
       GROUP BY col3;
   

5. 再次查看表 test_tbl2 上物化视图日志的信息，可以观察到表 test_tbl2 的 mlog 表定义中包含了 col2 列和 col3 列。

   obclient> DESC mlog$_test_tbl2;
   

   返回结果如下：

   +------------+---------------------+------+------+---------+-------+
   | FIELD      | TYPE                | NULL | KEY  | DEFAULT | EXTRA |
   +------------+---------------------+------+------+---------+-------+
   | COL2       | NUMBER(38)          | YES  | NULL | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)          | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 )        | NO   | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 )        | NO   | NULL | NULL    | NULL  |
   | M_ROW$$    | BIGINT(20) UNSIGNED | NO   | PRI  | NULL    | NULL  |
   | COL3       | NUMBER(38)          | YES  | NULL | NULL    | NULL  |
   +------------+---------------------+------+------+---------+-------+
   6 rows in set
   

6. 删除物化视图 mv1_test_tbl2。

   obclient> DROP MATERIALIZED VIEW mv1_test_tbl2;
   

7. 查看表 test_tbl2 上物化视图日志的信息。

   obclient> DESC mlog$_test_tbl2;
   

   返回结果如下：

   +------------+---------------------+------+------+---------+-------+
   | FIELD      | TYPE                | NULL | KEY  | DEFAULT | EXTRA |
   +------------+---------------------+------+------+---------+-------+
   | COL2       | NUMBER(38)          | YES  | NULL | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)          | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 )        | NO   | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 )        | NO   | NULL | NULL    | NULL  |
   | M_ROW$$    | BIGINT(20) UNSIGNED | NO   | PRI  | NULL    | NULL  |
   | COL3       | NUMBER(38)          | YES  | NULL | NULL    | NULL  |
   +------------+---------------------+------+------+---------+-------+
   6 rows in set
   

8. 删除物化视图 mv2_test_tbl2。

   obclient> DROP MATERIALIZED VIEW mv2_test_tbl2;
   

9. 查看表 test_tbl3 上物化视图日志的信息，可以观察到表 test_tbl2 的 mlog 表已经不存在了。

   obclient> DESC mlog$_test_tbl2;
   

   返回结果如下：

   OBE-04043: object TEST_USER001.MLOG$_TEST_TBL2 does not exist
   

手动管理物化视图日志

使用权限

• 创建物化视图日志需要对基表的 SELECT 权限和 CREATE TABLE 的权限。
• 修改物化视图日志需要对基表的 ALTER 权限。
• 删除物化视图日志需要 DROP TABLE 的权限。
• 物化视图日志只能赋予 SELECT 权限，不支持其他的 DML 操作。

创建物化视图日志

权限要求

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

语法

创建物化视图日志的 SQL 语句格式如下：

CREATE MATERIALIZED VIEW LOG ON [schema.] table_name
    [parallel_clause]
    [with_clause]
    [mv_log_purge_clause];

参数说明：

• table_name：指定物化视图日志对应的基表名称。
• parallel_clause：可选项，用于指定物化视图日志清理的并行度。
• with_clause：可选项，指定物化视图日志中包含的辅助列。
• mv_log_purge_clause：可选项，指定物化视图日志中数据的清除时间。

有关创建物化视图日志语法的详细参数说明信息，请参见 CREATE MATERIALIZED VIEW LOG。

示例如下：

1. 创建表 tbl1。

   CREATE TABLE tbl1 (col1 NUMBER, col2 VARCHAR2(20), col3 NUMBER, PRIMARY KEY(col1, col3))
       PARTITION BY HASH(col3) PARTITIONS 10;
   

2. 在 tbl1 表上创建物化视图日志。指定并行处理物化视图日志的并行度为 5 和物化视图日志记录 col2 列的变更信息，并且会记录变更前后的新值；配置物化视图日志从当前日期开始，每隔 1 天清理一次过期的物化视图日志记录。

   CREATE MATERIALIZED VIEW LOG ON tbl1 
     PARALLEL 5 
     WITH SEQUENCE(col2) INCLUDING NEW VALUES
     PURGE START WITH current_date NEXT current_date + 1;
   

3. 查看表 tbl1 上物化视图日志的信息。

   DESC mlog$_tbl1;
   

   返回结果如下：

   +------------+--------------+------+------+---------+-------+
   | FIELD      | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
   +------------+--------------+------+------+---------+-------+
   | COL1       | NUMBER       | NO   | PRI  | NULL    | NULL  |
   | COL2       | VARCHAR2(20) | YES  | NULL | NULL    | NULL  |
   | COL3       | NUMBER       | NO   | PRI  | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)   | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
   +------------+--------------+------+------+---------+-------+
   6 rows in set
   

修改物化视图日志

权限要求

执行 ALTER MATERIALIZED VIEW LOG 语句，需要当前用户拥有基表的 ALTER 权限。更多有关 OceanBase 数据库权限的详细介绍，请参见 Oracle 模式下的权限分类。

语法

修改物化视图日志的 SQL 语句格式如下：

ALTER MATERIALIZED VIEW LOG ON [schema.]table_name alter_mlog_action_list;

alter_mview_action_list:
    alter_mlog_action [, alter_mlog_action ...]

alter_mlog_action:
    parallel_clause
    | PURGE [[START WITH expr] [NEXT expr]]

parallel_clause:
    NOPARALLEL 
    | PARALLEL integer

参数说明：

• schema.：可选项，指定物化视图日志基表所在的 Schema。如果省略 schema.，则默认基表在当前会话所在的 Schema 中。
• table_name：指定物化视图日志对应的基表名称。
• alter_mlog_action_list：表示可以对物化视图日志执行修改的操作列表。可以同时指定多个操作，使用英文逗号（,）分隔。

有关修改物化视图日志语法的详细参数说明信息，请参见 ALTER MATERIALIZED VIEW LOG。

示例如下：

1. 创建表 test_tbl1。

   CREATE TABLE test_tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER, col4 BLOB);
   

2. 在 test_tbl1 表上创建物化视图日志。

   CREATE MATERIALIZED VIEW LOG ON test_tbl1 
       WITH SEQUENCE(col2, col3, col4) INCLUDING NEW VALUES;
   

3. 修改表 test_tbl1 上物化视图日志的并行度为 5。

   ALTER MATERIALIZED VIEW LOG ON test_tbl1 PARALLEL 5;
   

4. 修改表 test_tbl1 上物化视图日志从当前日期开始，每隔 1 天清理一次过期的物化视图日志记录。

   ALTER MATERIALIZED VIEW LOG ON test_tbl1
       PURGE START WITH current_date NEXT current_date + 1;
   

删除物化视图日志

注意事项

• 删除物化视图日志时，如果基表正处于某个运行的事务中，则直到该事务结束前，删除操作都会阻塞。
• 单独删除物化视图日志的时候，物化视图不会进入回收站。

权限要求

删除物化视图日志需要有 DROP TABLE 权限。更多有关 OceanBase 数据库权限的详细介绍，请参见 Oracle 模式下的权限分类。

语法

删除物化视图日志的 SQL 语句格式如下：

DROP MATERIALIZED VIEW LOG ON [schema.] table;

参数说明：

• schema.：可选项，指定物化视图日志基表所在的 Schema。如果省略 schema.，则默认基表在您自己的 Schema 中。
• table：指定物化视图日志对应的基表名称。

示例如下：

删除表 tbl1 上的物化视图日志。

DROP MATERIALIZED VIEW LOG ON tbl1;

示例

本示例将展示如何创建普通表、物化视图日志、增量刷新的物化视图，并且介绍如何删除物化视图日志以及增量刷新物化视图的操作信息。

1. 创建表 test_tbl1。

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

2. 在 test_tbl1 表上创建物化视图日志，指定使用序列号（SEQUENCE）来标识变化的数据，列部分指定了要记录的列，其中包括了 col2 和 col3。

   CREATE MATERIALIZED VIEW LOG ON test_tbl1
       WITH SEQUENCE (col2, col3) INCLUDING NEW VALUES;
   

3. 创建名为 mv_test_tbl1 的物化视图，定义物化视图为增量刷新，自动刷新物化视图的间隔为 5 分钟；在查询部分，指定了从 test_tbl1 表中按照 col2 列进行分组，并计算每个分组中的记录数（cnt）、非空 col3 列的记录数（cnt_col3）和 col3 列的总和（sum_col3）作为物化视图的结果。

   CREATE MATERIALIZED VIEW mv_test_tbl1
     REFRESH FAST ON DEMAND
     START WITH current_date NEXT current_date + interval '5' minute
     AS SELECT col2, COUNT(*) cnt, COUNT(col3) cnt_col3, SUM(col3) sum_col3 
       FROM test_tbl1 
       GROUP BY col2;
   

4. 查看表 test_tbl1 物化视图日志日志信息。

   SELECT * FROM sys.DBA_MVIEW_LOGS WHERE MASTER = 'TEST_TBL1';
   

   注意

   Oracle 模式中视图 sys.DBA_MVIEW_LOGS 中字段 MASTER 匹配表名时，表名需使用大写字母。

   返回结果如下：

   +--------------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+-----------------+-------------------+-----------------+------------------+-------------+-----------+-----------------+
   | LOG_OWNER    | MASTER    | LOG_TABLE       | LOG_TRIGGER | ROWIDS | PRIMARY_KEY | OBJECT_ID | FILTER_COLUMNS | SEQUENCE | INCLUDE_NEW_VALUES | PURGE_ASYNCHRONOUS | PURGE_DEFERRED | PURGE_START | PURGE_INTERVAL | LAST_PURGE_DATE | LAST_PURGE_STATUS | NUM_ROWS_PURGED | COMMIT_SCN_BASED | STAGING_LOG | PURGE_DOP | LAST_PURGE_TIME |
   +--------------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+-----------------+-------------------+-----------------+------------------+-------------+-----------+-----------------+
   | TEST_USER001 | TEST_TBL1 | MLOG$_TEST_TBL1 | NULL        | NO     | YES         | NO        | YES            | YES      | YES                | NO                 | NO             | NULL        | NULL           | 03-SEP-25       |                 0 |               0 | YES              | NO          |         1 |               0 |
   +--------------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+-----------------+-------------------+-----------------+------------------+-------------+-----------+-----------------+
   1 row in set
   

5. 删除表 test_tbl1 上的物化视图日志。

   DROP MATERIALIZED VIEW LOG ON test_tbl1;
   

6. 删除物化视图 mv_test_tbl1。

   DROP MATERIALIZED VIEW mv_test_tbl1;
   

相关文档

• ALL_MVIEW_LOGS
• 物化视图概述
• 创建物化视图
• 刷新物化视图
• 物化视图查询改写
• 查询物化视图
• 删除物化视图