Materialized view log

A materialized view log (mlog) records incremental update data of base tables (ordinary tables or materialized views) to support the fast refresh feature of materialized views. A materialized view log is a record table that tracks changes in the base table and applies the changes to the corresponding materialized view.

If the automatic management feature of materialized view logs is enabled, OceanBase Database automatically creates a materialized view log or updates the materialized view log definition when you create an incremental refresh materialized view or a real-time materialized view.

Limitations

• You can create a materialized view log only on a base table or materialized view.

• A base 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.

• Materialized view logs support columns of the LOB type, but only for inline storage of LOB data. For more information about LOB types, see LOB types.

  Notice

  For OceanBase Database V4.3.5, materialized view logs support LOB columns starting from V4.3.5 BP1.

• The materialized view log does not support JSON, XML, GIS, and UDT data.

• The materialized view log does not support generated columns (including virtual and non-virtual generated columns).

• The materialized view log does not support partitioning.

• The name of a materialized view log cannot exceed 64 characters in length. The name of the base table cannot exceed 58 characters in length, because the name of the materialized view log is prefixed with mlog$_.

• Table-level restore is not supported for materialized view logs.

• When a materialized view log is deleted separately, it does not go to the recycle bin.

• ALTER operations are not supported for materialized view logs after they are created.

• Indexes cannot be created on materialized view logs.

• All DML operations are not supported on materialized view logs; otherwise, an error will be returned.

Privileges

• 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.
• You can grant only the SELECT privilege on a materialized view log. Other DML privileges are not supported.

Schema definition for the 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 of a materialized view log is defined as follows:

Manage existing materialized view logs

• You can query the schema where the materialized view log resides for its structure and data.
• You can execute the DBMS_MVIEW.PURGE_LOG(table_name) procedure to purge the materialized view log of a base table.
• If the size of a materialized view log exceeds the available disk space, an error will be returned. In this case, you must drop and recreate the materialized view log.

Impact on materialized view logs of operations on base tables

Base table DML operations

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

• If you perform an INSERT operation on the base table, each inserted row will generate a corresponding record in the materialized view log. The dmltype$$ column value of this record will be I, and the old_new$$ column value will be N.
• If you perform a DELETE operation on the base table, a record is inserted into the materialized view log for each deleted row. The dmltype$$ column value of these records is D, and the old_new$$ column value is O.
• When you perform an UPDATE operation on a base table, each modified row generates two records in the materialized view log. The first record contains the old value of the UPDATEd row, with the dmltype$$ column set to U and the old_new$$ column set to O. The second record contains the new value after the UPDATE, with the dmltype$$ column set to U and the old_new$$ column set to N.

Data DDL operations

Before you drop a base table, you must drop its corresponding materialized view log. Otherwise, an error will be reported. The materialized view log is bound to the base table.

For more information about DDL operations supported by base tables, see Overview of online DDL operations 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 OceanBase Database privileges, see Privilege types in MySQL mode.

Grammar

The SQL statement for creating a materialized view log is in the following format:

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

Parameter description:

• OR REPLACE: optional. Specifies whether to create a new mlog if the corresponding mlog already exists. If this option is specified, the new mlog will replace the existing mlog. The process of recreating the mlog will not affect normal read or write operations on the materialized view or the base table.

  Note

  For OceanBase Database V4.3.5, the OR REPLACE option is supported starting from V4.3.5 BP3.

• table_name: the name of the base table that corresponds to the materialized view log.

• parallel_clause: the degree of parallelism for materialized view log cleanup.

• WITH clause: Optional. It specifies the columns to be included in the materialized view log.

• mv_log_purge_clause: Specifies the data cleanup time for the materialized view log. This parameter is optional.

For more information about the syntax of creating a materialized view log, see CREATE MATERIALIZED VIEW LOG.

Here is an example:

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. Set the degree of parallelism for parallelizing the materialized view log to 5, specify to record the change information of the col2 column, and indicate that the new values before and after the change are recorded; and configure the materialized view log to be cleared on a daily basis, starting from the current date, for the outdated materialized view log records.

   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 materialized view log information 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

Privileges

To execute the ALTER MATERIALIZED VIEW LOG statement, the current user must have the ALTER privilege on the target base table. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Syntax

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

The parameters are described as follows:

• database.: optional. The name of the database where the materialized view log is located. If you omit database., the default base table in the database connected by the current session is used.
• table_name: the name of the base table corresponding to the materialized view log.
• alter_mlog_action_list: the list of actions that can be performed on the materialized view log. You can specify multiple actions at a time, separated by commas (,).

For more information about the syntax for modifying materialized view logs, see ALTER MATERIALIZED VIEW LOG.

Here is an example:

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 based on the test_tbl1 table.

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

3. Set the degree of parallelism of 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 purge expired materialized view log records on a daily basis starting from the current date.

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

5. Modify 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

• When you drop a materialized view log, if the base table is involved in an ongoing transaction, the drop operation is blocked until the transaction is completed.
• When you delete a materialized view log, it does not go to the recycle bin immediately.

Privilege requirements

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

Syntax

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

DROP MATERIALIZED VIEW LOG ON [database.] table;

Description of parameters:

• database.: Optional. Specifies the database where the base table for the materialized view log resides. If you omit database., the base table is assumed to be in your current database by default.
• table: specifies the name of the base table corresponding to the materialized view log.

Here is an example:

Drop the materialized view log on the tbl1 table.

DROP MATERIALIZED VIEW LOG ON tbl1;

Examples

This topic describes how to create a table, a materialized view log, an incrementally refreshed materialized view, and how to drop the materialized view log and the 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 for the test_tbl1 table. Specify the sequence number (SEQUENCE) as the identifier for changes, and specify the columns to be recorded, which are 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 and automatic refresh every 5 minutes. In the query section, group records in test_tbl1 by col2 and calculate the number of records (cnt), the number of non-null values in col3 (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 of the test_tbl1 table.

   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 of the test_tbl1 table.

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