物化视图日志

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

使用限制

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

使用权限

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

在删除基表之前，需要先删除相应的物化视图日志，否则会报错。因为物化视图日志与基表有一一绑定关系，所以不支持直接删除基表只保留物化视图日志。

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

创建物化视图日志

权限要求

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

语法

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

CREATE MATERIALIZED VIEW LOG ON [database.] 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 INT, col2 VARCHAR(20), col3 INT, 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 sysdate() NEXT sysdate() + interval 1 day;
   

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

   DESC mlog$_tbl1;
   

   返回结果如下：

   +------------+-------------+------+------+---------+-------+
   | Field      | Type        | Null | Key  | Default | Extra |
   +------------+-------------+------+------+---------+-------+
   | col1       | int(11)     | NO   | PRI  | NULL    |       |
   | col2       | varchar(20) | YES  |      | NULL    |       |
   | col3       | int(11)     | NO   | PRI  | NULL    |       |
   | SEQUENCE$$ | bigint(20)  | NO   | PRI  | NULL    |       |
   | DMLTYPE$$  | varchar(1)  | YES  |      | NULL    |       |
   | OLD_NEW$$  | varchar(1)  | YES  |      | NULL    |       |
   +------------+-------------+------+------+---------+-------+
   6 rows in set
   

修改物化视图日志

权限要求

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

语法

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

ALTER MATERIALIZED VIEW LOG ON [database.]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]]
    | LOB_INROW_THRESHOLD [=] integer

parallel_clause:
    NOPARALLEL 
    | PARALLEL integer

参数说明：

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

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

示例如下：

1. 创建表 test_tbl1。

   CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT, col4 TEXT);
   

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 sysdate() NEXT sysdate() + INTERVAL 1 DAY;
   

5. 修改表 test_tbl1 上物化视图日志 LOB 内联存储长度阈值。

   ALTER MATERIALIZED VIEW LOG ON test_tbl1 LOB_INROW_THRESHOLD 10000;
   

删除物化视图日志

注意事项

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

权限要求

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

语法

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

DROP MATERIALIZED VIEW LOG ON [database.] table;

参数说明：

• database.：可选项，指定物化视图日志基表所在的数据库。如果省略 database.，则默认基表在您自己的数据库中。
• 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 sysdate() NEXT sysdate() + 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 oceanbase.DBA_MVIEW_LOGS WHERE MASTER = 'test_tbl1';
   

   返回结果如下：

   +-----------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+---------------------+-------------------+-----------------+------------------+-------------+
   | 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 |
   +-----------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+---------------------+-------------------+-----------------+------------------+-------------+
   | db_test   | test_tbl1 | mlog$_test_tbl1 | NULL        | NO     | YES         | NO        | YES            | YES      | YES                | NO                 | NO             | NULL        | NULL           | 2024-12-16 16:50:27 |                 0 |               0 | YES              | NO          |
   +-----------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+---------------------+-------------------+-----------------+------------------+-------------+
   1 row in set
   

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

   DROP MATERIALIZED VIEW LOG ON test_tbl1;
   

6. 删除物化视图 mv_test_tbl1。

   DROP MATERIALIZED VIEW mv_test_tbl1;
   

相关文档

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