Materialized view logs

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

If the automatic management of materialized view logs is enabled, OceanBase Database automatically creates an MLOG 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.
• When you create a materialized view log, if a transaction is in progress on the base table, the creation operation will be blocked until the transaction ends.
• 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 a materialized view log are bound to the partitions 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. Since the name of a materialized view log is prefixed with mlog$_, the name of the base table must not exceed 58 characters.
• A materialized view log does not support table-level recovery.
• When a materialized view log is deleted separately, it will not be moved to the recycle bin.
• After a materialized view log is created, it does not support the ALTER operation.
• No index can be created on a materialized view log.
• No DML operations can be performed on a materialized view log. An error will be returned.

Privileges

• To create a materialized view log, you must have the SELECT privilege and the CREATE TABLE privilege 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.
• Only the SELECT privilege can be granted 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 is mlog$_table, where table is the name of the base table.

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

Operations on existing materialized view logs

• You can directly query the schema of the materialized view log and the data in it.
• You can call DBMS_MVIEW.PURGE_LOG(table_name) to execute the PURGE operation on the materialized view log of a base table.
• If the size of the 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.

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

DML operations on the base table

The schema definition of the materialized view log is responsible for recording 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:

• When you execute an INSERT operation on the base table, a record corresponding to each inserted row will be inserted into the materialized view log. The dmltype$$ column of the record is I, and the old_new$$ column is N.
• When you execute a DELETE operation on the base table, a record corresponding to each deleted row will be inserted into the materialized view log. The dmltype$$ column of the record is D, and the old_new$$ column is O.
• When you execute an UPDATE operation on the base table, two records corresponding to each modified row will be inserted into the materialized view log: one for the old value before the UPDATE operation and one for the new value after the UPDATE operation. The dmltype$$ column of the record for the old value is U, and the old_new$$ column is O. The dmltype$$ column of the record for the new value 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 because a materialized view log is bound to a 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

Privileges

You must have the CREATE TABLE and SELECT privileges on the base table to create a materialized view log. For more information about the privileges of OceanBase Database, see Privilege classification in Oracle mode.

Syntax

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

CREATE [OR REPLACE] MATERIALIZED VIEW LOG ON [schema.] 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 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 data in the materialized view log is purged.

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

Here is an example:

1. Create a table named tbl1.

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

2. Create a materialized view log on the tbl1 table. Set the parallelism of the materialized view log to 5 and specify that the col2 column in the materialized view log records the change information. The change information includes the values before and after the change. Configure the materialized view log to purge 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 current_date NEXT current_date + 1;
   

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

   DESC mlog$_tbl1;
   

   The return result is as follows:

   +------------+--------------+------+------+---------+-------+
   | FIELD      | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
   +------------+--------------+------+------+---------+-------+
   | COL1       | NUMBER       | NO   | PRI  | NULL    | NULL  |
   | COL2       | VARCHAR2(20) | YES  | NULL | NULL    | NULL  |
   | COL3       | NUMBER       | NO   | PRI  | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)   | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
   +------------+--------------+------+------+---------+-------+
   6 rows in set
   

Modify a materialized view log

Privileges

You must have the ALTER privilege on the base table to execute the ALTER MATERIALIZED VIEW LOG statement. For more information about the privileges of OceanBase Database, see Privilege classification in Oracle mode.

Syntax

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

ALTER MATERIALIZED VIEW LOG ON [schema.]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]]

parallel_clause:
    NOPARALLEL 
    | PARALLEL integer

Parameters:

• schema.: optional. The schema where the base table of the materialized view log is located. If you omit this clause, the base table is located in the schema of the current session by default.
• 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:

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER, col4 BLOB);
   

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 of the materialized view log on the test_tbl1 table to 5.

   ALTER MATERIALIZED VIEW LOG ON test_tbl1 PARALLEL 5;
   

4. Configure the materialized view log on the test_tbl1 table to purge expired records every 1 day starting from the current date.

   ALTER MATERIALIZED VIEW LOG ON test_tbl1
       PURGE START WITH current_date NEXT current_date + 1;
   

Drop a materialized view log

Considerations

• If a transaction is in progress on the base table when you drop a materialized view log, the drop operation is blocked until the transaction is completed.
• When you drop a materialized view log, the materialized view is not moved to the recycle bin.

Privileges

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

Syntax

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

DROP MATERIALIZED VIEW LOG ON [schema.] table;

Parameters:

• schema.: optional. The schema where the base table of the materialized view log is located. If you omit this clause, the base table is located in your schema by default.
• 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 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 a sequence number (SEQUENCE) to identify changed data. The columns specified 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, defining it for incremental refresh with an automatic refresh interval of 5 minutes. In the query section, specify grouping by the col2 column and calculating the number of records (cnt), the number of non-null col3 records (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 current_date NEXT current_date + 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 sys.DBA_MVIEW_LOGS WHERE MASTER = 'TEST_TBL1';
   

   Notice

   In Oracle mode, when the MASTER field in the sys.DBA_MVIEW_LOGS view matches the table name, the table name must be in uppercase.

   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_USER001 | TEST_TBL1 | MLOG$_TEST_TBL1 | NULL        | NO     | YES         | NO        | YES            | YES      | YES                | NO                 | NO             | NULL        | NULL           | 03-SEP-25       |                 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

• ALL_MVIEW_LOGS
• Overview of Materialized Views
• Create Materialized Views
• Refresh Materialized Views
• Materialized View Query Rewriting
• Query Materialized Views
• Delete Materialized Views