Materialized view logs

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 for materialized view logs is enabled, OceanBase Database will automatically create the mlog or automatically update the mlog definition when creating an incrementally refreshed 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.

• A materialized view log is not moved to the recycle bin when it is dropped.

• You cannot perform an ALTER operation on a materialized view log 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 is 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 of materialized view logs

A table can have only one materialized view log whose 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 and data of the schema where a materialized view log is located.
• You can execute the DBMS_MVIEW.PURGE_LOG(table_name) procedure to purge the materialized view log of a table.
• If the size of a materialized view log exceeds the available disk space, an error is returned. In this case, you must drop and recreate the materialized view log to continue using it.

Base table operations on materialized view logs

DML operations on base tables

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

• When you perform an INSERT operation on the base table, a record is inserted into the materialized view log table for each row inserted into the base table. The dmltype$$ column value of the record is I, and the old_new$$ column value is N.
• When you perform a DELETE operation on the base table, a record is inserted into the materialized view log table for each row deleted from the base table. The dmltype$$ column value of the record is D, and the old_new$$ column value is O.
• When you perform an UPDATE operation on the base table, two records are inserted into the materialized view log table for each row updated. The first record is for the old value of the row before the UPDATE operation, and the dmltype$$ column value is U, and the old_new$$ column value is O. The second record is for the new value of the row after the UPDATE operation, and the dmltype$$ column value is U, and the old_new$$ column value is N.

DDL operations on base tables

You must drop the corresponding materialized view log before you drop the base table. Otherwise, an error is returned. Because a materialized view log is bound to a base table, you cannot drop the base table and retain the materialized view log.

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

Create a materialized view log

Privileges

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 privileges in OceanBase Database, see Privilege types in Oracle mode.

Syntax

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

The parameters are described as follows:

• 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 for which to create a materialized view log.

• parallel_clause: optional. Specifies the degree of parallelism for purging the materialized view log.

• with_clause: optional. Specifies the auxiliary columns to be included in the materialized view log.

• mv_log_purge_clause: optional. Specifies the time for purging expired records in the materialized view log.

For more information about the parameters, 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 degree of parallelism for purging the materialized view log to 5, and specify that the materialized view log record the changes of the col2 column, including the new values before and after the changes. Configure the materialized view log to purge expired records on a daily basis 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 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       | 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

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

Syntax

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

The parameters are described as follows:

• schema.: optional. The schema where the base table corresponding to the materialized view log is located. If you omit schema., the default base table in the schema where the session is located 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 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 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 current_date NEXT current_date + 1;
   

Drop a materialized view log

Considerations

• If a transaction is running on the base table when you drop a materialized view log, the drop operation will be blocked until the transaction ends.
• A materialized view log is not moved to the recycle bin when it is dropped.

Privileges

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

Syntax

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

DROP MATERIALIZED VIEW LOG ON [database.] table;

The parameters are described as follows:

• schema.: optional. The name of the schema where the base table for which to drop the materialized view log is located. If you do not specify schema., the base table is assumed to be in your own schema.
• table: the name of the base table for which to drop 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. Define the materialized view as an incrementally refreshed one with an automatic refresh interval of 5 minutes. In the query section, specify to group the records in the test_tbl1 table by the col2 column and calculate the number of records (cnt), the number of non-null records in the col3 column (cnt_col3), and the sum of the col3 column (sum_col3) as the result 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 of the test_tbl1 table.

   SELECT * FROM sys.DBA_MVIEW_LOGS WHERE MASTER = 'TEST_TBL1';
   

   Notice

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

   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_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. 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

• ALL_MVIEW_LOGS
• Overview of materialized views
• Create a materialized view
• Refresh a materialized view
• Query rewriting for materialized views
• Query a materialized view
• Drop a materialized view