Materialized view logs

A materialized view log (mlog) records incremental updates to the base table to support fast refresh of materialized views. An mlog is a record table that tracks changes in the base table and applies these changes to the corresponding materialized view to enable fast refresh.

If the automatic management of materialized view logs is enabled, OceanBase Database automatically creates or updates the mlog definition when you create a materialized view with incremental refresh or a real-time materialized view. For more information about the automatic management of materialized view logs, see Automatic management of materialized view logs.

Limitations

• You can create a materialized view log only on a regular table or a materialized view.
• A regular table can be bound to only one materialized view log.
• If a transaction is running on the base table when you create a materialized view log, the creation operation will be blocked until the transaction is completed.
• A materialized view log supports LOB columns, but only supports inline storage of LOB data. For more information about LOB types, see LOB types.
• A materialized view log does not support the following four types of data: JSON, XML, GIS, and UDT.
• A materialized view log does not support generated columns (including virtual and non-virtual columns).
• A materialized view log does not support specifying partitions. The partitions of the materialized view log are bound to those of the base table.
• The maximum length of the name of a materialized view log is the same as that of a regular table, which cannot exceed 64 characters. The name of the base table for a materialized view log must be less than or equal to 58 characters because the name of the materialized view log will be prefixed with mlog$_.
• A materialized view log does not support table-level restore.
• A materialized view log will not be moved to the recycle bin when it is deleted.
• After a materialized view log is created, you cannot perform the ALTER operation on it.
• You cannot create an index on a materialized view log.
• You cannot perform DML operations on a materialized view log. An error will be returned.

Privileges

• You must have the SELECT and CREATE TABLE privileges on the base table to create a materialized view log.
• You must have the ALTER privilege on the base table to modify a materialized view log.
• You must have the DROP TABLE privilege to drop a materialized view log.
• You can only grant the SELECT privilege to a materialized view log. Other DML operations are not supported.

Schema definition of a materialized view log

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

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

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

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

Operations on an existing materialized view log

• You can directly query the schema of the materialized view log and the data in it.
• You can execute the PURGE operation on a materialized view log of a base table by using DBMS_MVIEW.PURGE_LOG(table_name).
• If the size of a materialized view log exceeds the available disk space, an error will be returned. In this case, you must delete the materialized view log and recreate it before you can use it.

Impact of operations on the base table on the materialized view log

DML operations on the base table

The materialized view log is defined to record DML operations on the base table. Therefore, the INSERT, DELETE, and UPDATE operations on the base table will be recorded in the materialized view log, as follows:

• If you perform an INSERT operation on the base table, each row of data inserted will also be recorded in the materialized view log. The dmltype$$ column of the record will be I, and the old_new$$ column will be N.
• If you perform a DELETE operation on the base table, each row of data deleted will also be recorded in the materialized view log. The dmltype$$ column of the record will be D, and the old_new$$ column will be O.
• If you perform an UPDATE operation on the base table, each row of data modified will be recorded in two rows of data in the materialized view log. The first row records the old value of the row before the UPDATE operation. The dmltype$$ column of this row will be U, and the old_new$$ column will be O. The second row records the new value of the row after the UPDATE operation. The dmltype$$ column of this row will be U, and the old_new$$ column will be N.

DDL operations on the base table

Before you delete the base table, you must delete the corresponding materialized view log. Otherwise, an error will be returned. This is because a materialized view log is bound to the base table. Therefore, you cannot delete the base table while retaining the materialized view log.

For more information about DDL operations supported by the base table, see Online DDL and offline DDL operations.

Create a materialized view log

Privilege requirements

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

Syntax

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

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

Parameters:

• OR REPLACE: optional. If you specify this clause, OceanBase Database creates a new materialized view log with the same name as the existing one and replaces the existing one with the new one. During the recreation of the materialized view log, the read/write operations on the materialized view and the base table are not affected.
• table_name: specifies the name of the base table.
• parallel_clause: optional. Specifies the parallelism for purging the materialized view log.
• with_clause: optional. Specifies the auxiliary columns in the materialized view log.
• mv_log_purge_clause: optional. Specifies the time when the data in the materialized view log is purged.

For more information about the parameters in the syntax, see CREATE MATERIALIZED VIEW LOG.

Examples:

1. Create a table named 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 the tbl1 table. Specify the parallelism for purging the materialized view log as 5, and record the changes to the col2 column in the materialized view log. The changes to the col2 column are recorded in the materialized view log. The materialized view log is purged 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 the information about the materialized view log on the tbl1 table.

   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, you must have the ALTER privilege on the base table. For more information about the privileges of OceanBase Database, see Privilege types in MySQL-compatible mode.

Syntax

The SQL statement 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 you omit this clause, the base table is assumed to be in the current database.
• table_name: specifies the name of the base table.
• alter_mlog_action_list: specifies the list of operations that can be performed on the materialized view log. You can specify multiple operations separated by commas (,).

For more information about the parameters in the syntax, see ALTER MATERIALIZED VIEW LOG.

Examples:

1. Create a table named test_tbl1.

   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. Set the parallelism for purging the materialized view log on the test_tbl1 table to 5.

   ALTER MATERIALIZED VIEW LOG ON test_tbl1 PARALLEL 5;
   

4. Set the materialized view log on the test_tbl1 table to be purged 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. Set the inline storage length threshold for 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

• If a base table is involved in an ongoing transaction when you drop a materialized view log, the drop operation is blocked until the transaction ends.
• When you drop a materialized view log, the materialized view is not moved to the recycle bin.

Privileges

To drop a materialized view log, you must have the DROP TABLE privilege. For more information about the privileges of OceanBase Database, see Privilege types in MySQL-compatible mode.

Syntax

The SQL statement 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 you omit this clause, the base table is assumed to be in your own database.
• table: specifies the name of the base table.

Examples:

Drop the materialized view log on the tbl1 table.

DROP MATERIALIZED VIEW LOG ON tbl1;

Example

This example shows how to create a regular table, a materialized view log, and a materialized view with incremental refresh, and how to delete the materialized view log and the materialized view with incremental refresh.

This example shows how to create a regular table, a materialized view log, and an incrementally refreshed materialized view. It also covers how to delete a materialized view log and an incrementally refreshed materialized view.

1. Create a table named 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, specifying the use of sequence numbers (SEQUENCE) to identify changed data. The columns to be recorded include 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 with incremental refresh, set to automatically refresh every 5 minutes. In the query part, specify grouping by the col2 column from the test_tbl1 table and calculate the number of records in each group (cnt), the number of non-null records in the col3 column (cnt_col3), and the sum of the col3 column (sum_col3) as the results 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 the test_tbl1 table.

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

   The returned 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. Delete the materialized view log on the test_tbl1 table.

   DROP MATERIALIZED VIEW LOG ON test_tbl1;
   

6. Delete 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
• Materialized view query rewriting
• Query a materialized view
• Delete a materialized view