Materialized view logs|V4.6.0|OceanBase Database| docs|Distributed Database

A materialized view log (mlog) records incremental updates to the base table to support fast refreshes 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 refreshes.

If the automatic management of materialized view logs feature is enabled, OceanBase Database automatically creates or updates the mlog definition when you create a materialized view with incremental refresh or real-time refresh. 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 ends.
A materialized view log supports LOB columns, but only supports inline storage for 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 the partitions of the base table.
The maximum length of a materialized view log name is the same as that of a regular table, which cannot exceed 64 characters. Since the name of a materialized view log is prefixed with mlog$_, the name of the base table for creating a materialized view log cannot exceed 58 characters.
A materialized view log does not support table-level recovery.
A materialized view log will not be moved to the recycle bin when it is deleted.
A materialized view log does not support the ALTER operation after it is created.
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

To create a materialized view log, you must have the SELECT and CREATE TABLE privileges on the base table.
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.
You can only grant the SELECT privilege on 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:

Column	Type	Description
sequence$$	in64_t	An auto-increment column that serves as the primary key of the materialized view log (mlog). Note The primary key of mlog consists of the primary key of the base table, all partition keys (if any), and the auto-increment column `sequence$$`, forming a composite primary key.
primary key	Follows the base table	If the base table has a primary key, the primary key columns of the base table (which may include multiple columns if it is a composite primary key) are recorded in mlog.
dmltype$$	char(1)	Records the DML type. The value can be `I`, `D`, or `U`, which respectively represent `INSERT`, `DELETE`, and `UPDATE`.
old_new$$	char(1)	Marks the old and new values in an `UPDATE` statement. An `UPDATE` statement will write two rows of data to the materialized view log: one row for the old value before the `UPDATE` operation and one row for the new value after the `UPDATE` operation. The old value is marked with `O` and the new value is marked with `N`.
column 1	Follows the base table	The first regular column of the base table.
...	N/A	N/A
column N	Follows the base table	The Nth regular column of the base table.
ora_rowscn	N/A	A pseudo column that records a hidden column in the storage layer. It can be read.
m_row$$	uint64_t	This column is recorded in mlog only when the base table does not have a primary key. mlog must contain the primary key columns of the base table. If the base table does not have a primary key, the name of the hidden primary key in mlog is `M_ROW$$`.

Operations on existing materialized view logs

You can directly query the schema and data of the materialized view log.
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 capacity, an error will be returned. In this case, you must 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 the base table

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

When you execute 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 corresponding record is I, and the old_new$$ column is N.
When you execute 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 corresponding record is D, and the old_new$$ column is O.
When you execute an UPDATE operation on the base table, each row of data modified will generate two records in the materialized view log. The first record is the old value of the row before the UPDATE operation. The dmltype$$ column of the record is U, and the old_new$$ column is O. The second record is the new value of the row after the UPDATE operation. The dmltype$$ column of the record is U, and the old_new$$ column is 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 the DDL operations supported by the base table, see Online DDL and offline DDL operations.

Create a materialized view log

Note

OceanBase Database does not support specifying partitions for mlogs. The partitions of an mlog are bound to those of the base table.

Privilege requirements

You must have the CREATE TABLE and SELECT privileges on the base table to create a materialized view log. For more information about privileges in OceanBase Database, see Privilege types in MySQL 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, the system creates a new materialized view log with the same name as the existing one and replaces the existing one with the new one. The existing materialized view log is not deleted during the replacement. The read and write operations on the materialized view and base table are not affected.
table_name: the name of the base table.
parallel_clause: optional. Specifies the parallelism of the materialized view log cleanup.
with_clause: optional. Specifies the auxiliary columns in the materialized view log.
mv_log_purge_clause: optional. Specifies the time when the system clears the data in the materialized view log.

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

Here is an example:

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;

Create a materialized view log on the tbl1 table. Set the parallelism of the materialized view log to 5, record the changes to the col2 column, and record the values before and after the changes. Set the system to clear the expired materialized view log 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;
```

Query the information about the materialized view log on the tbl1 table.

DESC mlog$_tbl1;

The returned 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

You must have the ALTER privilege on the base table to execute the ALTER MATERIALIZED VIEW LOG statement. For more information about privileges in OceanBase Database, see Privilege types in MySQL 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. The name of the database where the materialized view is located. If you do not specify this parameter, the base table is assumed to be in the current database.
table_name: the name of the base table.
alter_mlog_action_list: 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.

Here is an example:

Create a table named test_tbl1.

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

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;

Set the parallelism of the materialized view log on the test_tbl1 table to 5.
```
ALTER MATERIALIZED VIEW LOG ON test_tbl1 PARALLEL 5;
```
Set the system to clear the expired materialized view log records every 1 day starting from the current date on the test_tbl1 table.
```
ALTER MATERIALIZED VIEW LOG ON test_tbl1
    PURGE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;
```
Set the LOB inline storage length threshold of 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 a running transaction, the drop operation is blocked until the transaction is completed.
When you drop a materialized view log, the corresponding materialized view is not moved to the recycle bin.

Privilege requirements

You must have the DROP TABLE privilege to drop a materialized view log. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Syntax

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

DROP MATERIALIZED VIEW LOG ON [database.] table;

Parameters:

database.: optional. The name of the database where the base table of the materialized view log is located. If you do not specify this parameter, the base table is assumed to be in your own database.
table: the name of the base table.

Here is an example:

Drop the materialized view log on the tbl1 table.

DROP MATERIALIZED VIEW LOG ON tbl1;

Example

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

Create a table named test_tbl1.

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

Create a materialized view log on the test_tbl1 table, specifying the use of a sequence number (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;
```
Create a materialized view named mv_test_tbl1, defining it for incremental refresh with an automatic refresh interval of 5 minutes. In the query section, 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;
```

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

Drop the materialized view log on the test_tbl1 table.
```
DROP MATERIALIZED VIEW LOG ON test_tbl1;
```
Drop the materialized view mv_test_tbl1.
```
DROP MATERIALIZED VIEW mv_test_tbl1;
```

OceanBase

Customer Stories

Documentation

Materialized view logs

Limitations

Privileges

Schema definition of a materialized view log

Note

Operations on existing materialized view logs

Impact of base table operations on materialized view logs

DML operations on the base table

DDL operations on the base table

Create a materialized view log

Note

Privilege requirements

Syntax

Modify a materialized view log

Privilege requirements

Syntax

Drop a materialized view log

Considerations

Privilege requirements

Syntax

Example

References