Materialized view logs

Materialized view logs (mlogs) record incremental update data of the base table to support the fast refresh feature of materialized views. An mlog is a tracking table that keeps track of changes in the base table and applies these changes to the corresponding materialized view, enabling fast refresh.

OceanBase Database supports both manual and automatic management of materialized view logs.

Limitations

• Materialized view logs can only be created on regular tables and materialized views.
• A base table is bound to only one materialized view log.
• If a transaction is running on the base table when you create a materialized view log for it, the creation operation is blocked until the transaction completes.
• Materialized view logs support columns of the LOB type, but only allow LOB data to be stored inline. For more information about LOB types, see LOB types.
• The following four data types are not currently supported in materialized view logs: JSON, XML, spatial data, and UDT.
• Generated columns (including both virtual and non-virtual columns) are not currently supported in materialized view logs.
• Specifying partitions is not currently supported for materialized view logs. Partitions are bound to the base table.
• The maximum length for a materialized view log name is the same as that for a regular table, which is 64 characters. Since the name of a materialized view log is prefixed with mlog$_, the base table name for creating the materialized view log cannot exceed 58 characters.
• Table-level recovery is not supported for materialized view logs.
• When deleted individually, materialized view logs do not go to the recycle bin.
• No ALTER operations are supported on materialized view logs after they are created.
• Indexes cannot be created on materialized view logs.
• Performing DML operations on materialized view logs will result in an error.

Schema definition of a materialized view log

A table can have only one materialized view log. Its schema name is mlog$_table, where table is the name of the base table.

The schema of a materialized view log is defined as follows:

Manipulating existing materialized view logs

• You can directly query the structure and data within the schema where the materialized view log resides.
• You can execute the PURGE operation on a materialized view log of a base table by using the DBMS_MVIEW.PURGE_LOG(table_name) procedure.
• If a materialized view log grows larger than the available disk capacity, an error is returned. In this case, you must first delete the materialized view log and recreate it before you can use it again.

Impact of base table operations on materialized view logs

DML operations on base tables

The purpose of a materialized view log is to record DML operations on the base table. Therefore, INSERT, DELETE, and UPDATE operations performed on the base table are ultimately recorded in the materialized view log, as follows:

• For each row inserted into the base table, a corresponding record is generated and inserted into the materialized view log. In this record, the dmltype$$ column value is I, and the old_new$$ column value is N.
• For each row deleted from the base table, a corresponding record is generated and inserted into the materialized view log. In this record, the dmltype$$ column value is D, and the old_new$$ column value is O.
• For each row updated in the base table, two corresponding records are generated and inserted into the materialized view log. The first record contains the old value of the row being updated. In this record, the dmltype$$ column value is U, and the old_new$$ column value is O. The second record contains the new value after the update. In this record, the dmltype$$ column value is U, and the old_new$$ column value is N.

DDL operations on base tables

• In versions earlier than V4.4.2 BP2, you must delete the corresponding materialized view log before deleting a base table; otherwise, an error is returned. Because materialized view logs are bound one-to-one with base tables, you cannot directly delete a base table while retaining only the materialized view log.
• For V4.4.2 and later, starting from V4.4.2 BP2, you can directly delete a base table without first deleting its corresponding materialized view log.

For more information about DDL operations supported on base tables, see Online DDL and offline DDL operations.

Automatic management of materialized view logs

OceanBase Database supports the automatic management of materialized view logs (mlogs). This feature includes the following aspects:

• When creating a materialized view, it analyzes the dependency of the materialized view on the base table and automatically creates the required mlog.
• In versions earlier than V4.4.2 BP2, the backend periodically trims mlogs, deletes unmaintained mlogs, and simplifies the column set in mlogs to reduce mlog maintenance costs.
• For V4.4.2, starting from V4.4.2 BP2, when deleting a materialized view (MV), if the mlog depended on by the MV is not depended on by other MVs, that mlog will be deleted.

OceanBase Database has the automatic management of materialized view logs enabled by default. When creating an incremental refresh materialized view or a real-time materialized view, OceanBase Database automatically creates an mlog or updates the mlog definition.

Limitations

When creating a materialized view, an mlog is automatically created or its definition is automatically updated only if the materialized view is explicitly declared as an incremental refresh materialized view (by specifying REFRESH FAST) or a real-time materialized view (by specifying ENABLE ON QUERY COMPUTATION).

Considerations

• In versions earlier than V4.4.2 BP2, when updating an mlog definition, OceanBase Database actually creates a new mlog table and replaces the original mlog. During this replacement process, an incremental refresh is required for the associated incremental refresh materialized views to flush the incremental data from the original mlog into the materialized views, allowing for a safe replacement of the mlog. Therefore, when a base table has many materialized views associated with it or contains a large amount of incremental data, updating an mlog definition can be a time-consuming operation and should be anticipated in advance.

  Note

  For V4.4.2, starting from V4.4.2 BP2, if a materialized view has newly dependent columns, the system will add columns online at the end, rather than creating a new mlog table and replacing the existing one.

• In versions earlier than V4.4.2 BP2, it is recommended not to set the mlog_trim_interval too small, as this may lead to incorrect mlog trimming, requiring the mlog to be recreated or updated when the materialized view is created.

Related configurations

OceanBase Database provides two parameters to control the automated behavior of mlogs:

• enable_mlog_auto_maintenance: controls whether to enable the automatic management of materialized view logs.

• mlog_trim_interval: controls the scheduling cycle for the background automatic trimming task of mlogs.

  Note

  For V4.4.2, mlog_trim_interval does not take effect starting from V4.4.2 BP2.

Examples

Enable the automatic management of mlogs.

obclient> ALTER SYSTEM SET enable_mlog_auto_maintenance = True;

Example 1: Automatically create an mlog

1. Create the table test_tbl1.

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

2. Directly create an incremental refresh materialized view mv_test_tbl1 on the test_tbl1 table. OceanBase Database automatically creates mlogs for the required col2 column in the test_tbl1 table.

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

3. View the information of the materialized view logs on the test_tbl1 table.

   obclient> DESC mlog$_test_tbl1;
   

   The return result is as follows:

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

Example 2: Automatically update the mlog definition

1. Create the table test_tbl2.

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

2. Directly create an incremental refresh materialized view mv1_test_tbl2 on the test_tbl2 table. OceanBase Database automatically creates mlogs for the required col2 column in the test_tbl2 table.

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

3. View the information of the materialized view logs on the test_tbl2 table.

   obclient> DESC mlog$_test_tbl2;
   

   The return result is as follows:

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

4. Directly create an incremental refresh materialized view mv2_test_tbl2 on the test_tbl2 table. OceanBase Database detects that the current mlog table contains only the col2 column, so it modifies the existing mlog definition to include the col3 column in the mlog table.

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

5. View the information of the materialized view logs on the test_tbl2 table again. You can observe that the mlog table definition for test_tbl2 now includes both the col2 and col3 columns.

   obclient> DESC mlog$_test_tbl2;
   

   The return result is as follows:

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

6. Drop the materialized view mv1_test_tbl2.

   obclient> DROP MATERIALIZED VIEW mv1_test_tbl2;
   

7. View the information of the materialized view logs on the test_tbl2 table.

   obclient> DESC mlog$_test_tbl2;
   

   The return result is as follows:

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

8. Drop the materialized view mv2_test_tbl2.

   obclient> DROP MATERIALIZED VIEW mv2_test_tbl2;
   

9. View the information of the materialized view logs on the test_tbl3 table. You can observe that the mlog table for test_tbl2 no longer exists.

   obclient> DESC mlog$_test_tbl2;
   

   The return result is as follows:

   ERROR 1146 (42S02): Table 'test_db.mlog$_test_tbl2' doesn't exist
   

Manually manage materialized view logs

Privileges required

• To create a materialized view log, you must have the SELECT privilege on the base table and the CREATE TABLE privilege.
• To modify a materialized view log, you must have the ALTER privilege on the base table.
• To drop a materialized view log, you must have the DROP TABLE privilege.
• Only the SELECT privilege can be granted on a materialized view log; other DML operations are not supported.

Create a materialized view log

Privilege requirements

To create a materialized view log, you must have the CREATE TABLE privilege and the SELECT privilege on the base table. For more information about OceanBase Database privileges, see Privilege types in MySQL-compatible mode.

Syntax

The SQL statement format for creating a materialized view log is as follows:

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

Parameter description:

• table_name: Specifies the name of the base table corresponding to the materialized view log.
• parallel_clause: Optional. Specifies the degree of parallelism for cleaning up the materialized view log.
• with_clause: Optional. Specifies auxiliary columns contained in the materialized view log.
• mv_log_purge_clause: Optional. Specifies the purge time for data in the materialized view log.

For detailed parameter descriptions on the syntax for creating a materialized view log, see CREATE MATERIALIZED VIEW LOG.

Example:

1. Create table tbl1.

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

2. Create a materialized view log on table tbl1. Specify a degree of parallelism of 5 for parallel processing of the materialized view log, and the materialized view log records changes in column col2, including both the new values before and after the change. Configure the materialized view log to clean up expired records every 1 day starting from the current date.

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

3. View information about the materialized view log on table tbl1.

   DESC mlog$_tbl1;
   

   The return result is as follows:

   +------------+-------------+------+------+---------+-------+
   | 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
   

Modify a materialized view log

Privilege requirements

To execute the ALTER MATERIALIZED VIEW LOG statement, the current user must have the ALTER privilege on the base table to be operated on. For more details about OceanBase Database privileges, see Privilege types in MySQL-compatible mode.

Syntax

The SQL statement format for modifying a materialized view log is as follows:

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

Parameters:

• database.: Optional. Specifies the database where the materialized view is located. If database. is omitted, the base table is assumed to be in the database connected by the current session.
• table_name: Specifies the name of the base table corresponding to the materialized view log.
• alter_mlog_action_list: Indicates the list of modification actions that can be performed on the materialized view log. Multiple actions can be specified, separated by commas (,).

For detailed parameter descriptions on the syntax for modifying a materialized view log, see ALTER MATERIALIZED VIEW LOG.

Examples:

1. Create the test_tbl1 table.

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

2. Create a materialized view log on the test_tbl1 table.

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

3. Modify the parallelism of the materialized view log on the test_tbl1 table to 5.

   ALTER MATERIALIZED VIEW LOG ON test_tbl1 PARALLEL 5;
   

4. Modify the materialized view log on the test_tbl1 table to clean up expired records every 1 day starting from the current date.

   ALTER MATERIALIZED VIEW LOG ON test_tbl1
       PURGE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;
   

5. Modify the threshold for inline storage length of LOB data in the materialized view log on the test_tbl1 table.

   ALTER MATERIALIZED VIEW LOG ON test_tbl1 LOB_INROW_THRESHOLD 10000;
   

Drop a materialized view log

Considerations

• When dropping a materialized view log, if the base table is involved in an ongoing transaction, the drop operation will be blocked until the transaction completes.
• When a materialized view log is dropped individually, the materialized view does not move to the recycle bin.

Privilege requirements

Dropping a materialized view log requires the DROP TABLE privilege. For more information about OceanBase Database privileges, see Privilege types in MySQL-compatible mode.

Syntax

The SQL statement format for dropping a materialized view log is as follows:

DROP MATERIALIZED VIEW LOG ON [database.] table;

Parameters:

• database.: Optional. Specifies the database where the base table of the materialized view log is located. If database. is omitted, the base table is in your own database by default.
• table: Specifies the name of the base table corresponding to the materialized view log.

Example:

Drop the materialized view log on table tbl1.

DROP MATERIALIZED VIEW LOG ON tbl1;

Examples

This example shows how to create a regular table, a materialized view log, and an incremental refresh materialized view. It also describes how to drop a materialized view log and perform operations on an incremental refresh materialized view.

1. Create table test_tbl1.

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

2. Create a materialized view log on the test_tbl1 table. Specify to use a sequence number (SEQUENCE) to identify changed data. In the column part, specify the columns to record, including col2 and col3.

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

3. Create a materialized view named mv_test_tbl1. Define the materialized view to be refreshed incrementally, with an automatic refresh interval of 5 minutes. In the query part, specify to group by the col2 column from the test_tbl1 table, and calculate the record count (cnt), the count of non-null col3 records (cnt_col3), and the sum of col3 (sum_col3) as the result of the materialized view.

   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. View the materialized view log information for table test_tbl1.

   SELECT * FROM oceanbase.DBA_MVIEW_LOGS WHERE MASTER = 'test_tbl1';
   

   The return result is as follows:

   +-----------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+---------------------+-------------------+-----------------+------------------+-------------+-----------+-----------------+
   | 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_db   | test_tbl1 | mlog$_test_tbl1 | NULL        | NO     | YES         | NO        | YES            | YES      | YES                | NO                 | NO             | NULL        | NULL           | 2025-09-03 14:13:06 |                 0 |               0 | YES              | NO          |         1 |               0 |
   +-----------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+---------------------+-------------------+-----------------+------------------+-------------+-----------+-----------------+
   1 row in set
   

5. Drop the materialized view log on table test_tbl1.

   DROP MATERIALIZED VIEW LOG ON test_tbl1;
   

6. Drop the materialized view mv_test_tbl1.

   DROP MATERIALIZED VIEW mv_test_tbl1;
   

References

• DBA_MVIEW_LOGS
• Overview of materialized views
• Create a materialized view
• Refresh a materialized view
• Query rewrite for materialized views
• Query materialized views
• Drop a materialized view