Materialized view logs (mlogs) record incremental update data of base tables to support the fast refresh feature of materialized views. An mlog is a tracking table that keeps track of changes in base tables and applies these changes to the corresponding materialized view, enabling fast refresh.
OceanBase AI Database supports both manual and automatic management of materialized view logs.
Limitations
- Materialized view logs can only be created on regular tables and materialized views.
- A base table is 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 is blocked until the transaction completes.
- Materialized view logs support columns of the LOB type, but only allow in-line storage for LOB data. For more information about LOB types, see LOB types.
- The following four data types are not currently supported in materialized view logs: JSON, XML, spatial data, and UDT.
- Generated columns (including virtual and non-virtual columns) are not currently supported in materialized view logs.
- Specifying partitions is not currently supported for materialized view logs; their partitions are bound to those of the base table.
- The maximum length for a materialized view log name is the same as that for a regular table, which cannot exceed 64 characters. Since the name of a materialized view log is prefixed with
mlog$_, the base table name for creating the materialized view log cannot exceed 58 characters. - Table-level recovery is not supported for materialized view logs.
- When deleted individually, materialized view logs do not go to the recycle bin.
- No
ALTERoperations are supported on materialized view logs after they are created. - Indexes cannot be created on materialized view logs.
- Performing DML operations on materialized view logs will result in an error.
Schema definition of a materialized view log
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 of a materialized view log is defined as follows:
Column name |
Type |
Description |
|---|---|---|
| sequence$$ | in64_t | An auto-increment column that serves as the primary key of the materialized view log (mlog).
NoteThe primary key of an mlog consists of the base table's primary key, all partitioning keys (if present), and the auto-increment column |
| 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 the 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 writes 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 the mlog only when the base table does not have a primary key. The 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 the 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
PURGEoperation on a materialized view log of a base table by using PURGE_LOG. - If the size of a materialized view log exceeds the available disk capacity, an error is 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 purpose of a materialized view log table is to record DML operations on the base table. Therefore, INSERT, DELETE, and UPDATE operations performed on the base table are ultimately recorded in the materialized view log, as follows:
- For each row inserted into the base table, a corresponding record is generated and inserted into the materialized view log. This record has a
dmltype$$column value ofIand anold_new$$column value ofN. - For each row deleted from the base table, a corresponding record is generated and inserted into the materialized view log. This record has a
dmltype$$column value ofDand anold_new$$column value ofO. - For each row updated in the base table, two corresponding records are generated and inserted into the materialized view log. The first record contains the old value of the row being updated, with a
dmltype$$column value ofUand anold_new$$column value ofO. The second record contains the new value after the update, with admltype$$column value ofUand anold_new$$column value ofN.
DDL operations on the base table
In OceanBase AI Database, you can directly delete a base table without first deleting the corresponding materialized view log.
For more information about DDL operations supported on base tables, see Online DDL and Offline DDL Operations.
Automatic management of materialized view logs
OceanBase AI Database supports automatic management of materialized view logs (mlogs). This feature includes the following aspects:
- When creating a materialized view, the system analyzes the dependency between the materialized view and its base table and automatically creates the required mlog.
- When you drop a materialized view (MV), if the mlog that the MV depends on is not used by other MVs, the mlog is deleted.
OceanBase AI Database enables automatic management of materialized view logs by default. When you create an incremental refresh materialized view or a real-time materialized view, OceanBase AI Database automatically creates an mlog or updates the mlog definition.
Limitations
When creating a materialized view, an mlog is automatically created or its definition is automatically updated only if the materialized view is explicitly declared as an incremental refresh materialized view (by specifying REFRESH FAST) or a real-time materialized view (by specifying ENABLE ON QUERY COMPUTATION).
Considerations
- If a materialized view has new dependent columns, the system updates the mlog online by appending the columns at the end of the mlog.
Related parameters
OceanBase AI Database provides the following parameter to control the automated behavior of mlogs:
- enable_mlog_auto_maintenance: Specifies whether to enable the automatic management of materialized view logs.
Examples
Enable automatic management of mlogs.
Note
For a newly created tenant, the default value of the enable_mlog_auto_maintenance parameter is True, which means the automatic management of mlogs is enabled by default.
obclient> ALTER SYSTEM SET enable_mlog_auto_maintenance = True;
Example 1: Automatically create an mlog
Create a table named
test_tbl1.obclient> CREATE TABLE test_tbl1(col1 INT, col2 INT, col3 INT);Directly create an incremental refresh materialized view named
mv_test_tbl1on thetest_tbl1table. OceanBase AI Database automatically creates an mlog for thecol2column on thetest_tbl1table.obclient> CREATE MATERIALIZED VIEW mv_test_tbl1 REFRESH FAST AS SELECT col2, count(*) cnt FROM test_tbl1 GROUP BY col2;View the information about the materialized view log on the
test_tbl1table.obclient> DESC mlog$_test_tbl1;The query result is as follows:
+------------+---------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+------+---------+-------+ | col2 | int(11) | YES | | NULL | | | SEQUENCE$$ | bigint(20) | NO | PRI | NULL | | | DMLTYPE$$ | varchar(1) | NO | | NULL | | | OLD_NEW$$ | varchar(1) | NO | | NULL | | | M_ROW$$ | bigint(20) unsigned | NO | PRI | NULL | | +------------+---------------------+------+------+---------+-------+ 5 rows in set
Example 2: Automatically update the mlog definition
Create the table
test_tbl2.obclient> CREATE TABLE test_tbl2(col1 INT, col2 INT, col3 INT);Directly create an incremental refresh materialized view named
mv1_test_tbl2on thetest_tbl2table. OceanBase AI Database automatically creates an mlog for thecol2column on thetest_tbl2table.obclient> CREATE MATERIALIZED VIEW mv1_test_tbl2 REFRESH FAST AS SELECT col2, count(*) cnt FROM test_tbl2 GROUP BY col2;View the information of the materialized view log on the
test_tbl2table.obclient> DESC mlog$_test_tbl2;The query result is as follows:
+------------+---------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+------+---------+-------+ | col2 | int(11) | YES | | NULL | | | SEQUENCE$$ | bigint(20) | NO | PRI | NULL | | | DMLTYPE$$ | varchar(1) | NO | | NULL | | | OLD_NEW$$ | varchar(1) | NO | | NULL | | | M_ROW$$ | bigint(20) unsigned | NO | PRI | NULL | | +------------+---------------------+------+------+---------+-------+ 5 rows in setDirectly create an incremental refresh materialized view
mv2_test_tbl2on thetest_tbl2table. OceanBase AI Database detects that the current mlog table contains only thecol2column, so it modifies the existing mlog definition to include thecol3column in the mlog table.obclient> CREATE MATERIALIZED VIEW mv2_test_tbl2 REFRESH FAST AS SELECT col3, count(*) cnt FROM test_tbl2 GROUP BY col3;View the information of the materialized view logs on the
test_tbl2table again. You can observe that the mlog table definition fortest_tbl2now includes both thecol2andcol3columns.obclient> DESC mlog$_test_tbl2;The query result is as follows:
+------------+---------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+------+---------+-------+ | col2 | int(11) | YES | | NULL | | | SEQUENCE$$ | bigint(20) | NO | PRI | NULL | | | DMLTYPE$$ | varchar(1) | NO | | NULL | | | OLD_NEW$$ | varchar(1) | NO | | NULL | | | M_ROW$$ | bigint(20) unsigned | NO | PRI | NULL | | | col3 | int(11) | YES | | NULL | | +------------+---------------------+------+------+---------+-------+ 6 rows in setDrop the materialized view
mv1_test_tbl2.obclient> DROP MATERIALIZED VIEW mv1_test_tbl2;View the information of the materialized view logs on the
test_tbl2table.obclient> DESC mlog$_test_tbl2;The query result is as follows:
+------------+---------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+------+---------+-------+ | col2 | int(11) | YES | | NULL | | | SEQUENCE$$ | bigint(20) | NO | PRI | NULL | | | DMLTYPE$$ | varchar(1) | NO | | NULL | | | OLD_NEW$$ | varchar(1) | NO | | NULL | | | M_ROW$$ | bigint(20) unsigned | NO | PRI | NULL | | | col3 | int(11) | YES | | NULL | | +------------+---------------------+------+------+---------+-------+ 6 rows in setDrop the materialized view
mv2_test_tbl2.obclient> DROP MATERIALIZED VIEW mv2_test_tbl2;View the information of the materialized view logs on the
test_tbl3table. You can observe that the mlog table fortest_tbl2no longer exists.obclient> DESC mlog$_test_tbl2;The query result is as follows:
ERROR 1146 (42S02): Table 'test_db.mlog$_test_tbl2' doesn't exist
Manually manage materialized view logs
Privileges
- To create a materialized view log, you must have the
SELECTprivilege on the base table and theCREATE TABLEprivilege. - To modify a materialized view log, you must have the
ALTERprivilege on the base table. - To drop a materialized view log, you must have the
DROP TABLEprivilege. - Only the
SELECTprivilege can be granted on a materialized view log; other DML operations are not supported.
Create a materialized view log
Note
The mlog feature in OceanBase AI Database does not currently support specifying partitions (Partition). The partition of an mlog is bound to that of its base table.
Privilege requirements
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 AI Database, see Permission classification.
Syntax
The SQL statement for creating a materialized view log is as follows:
CREATE MATERIALIZED VIEW LOG ON [database.] table_name
[parallel_clause]
[with_clause]
[mv_log_purge_clause];
The parameters are described as follows:
table_name: The name of the base table corresponding to the materialized view log.parallel_clause: Optional. Specifies the degree of parallelism for cleaning up the materialized view log.with_clause: Optional. Specifies auxiliary columns contained in the materialized view log.mv_log_purge_clause: Optional. Specifies the purge time for data in the materialized view log.
For detailed parameter descriptions of the syntax for creating a materialized view log, see CREATE MATERIALIZED VIEW LOG.
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
tbl1table. Specify a degree of parallelism of5for parallel processing of the materialized view log, and specify that the materialized view log records changes in columncol2, including both the new values before and after the change. Configure the materialized view log to clean up expired records every1day 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;View information about the materialized view log on the
tbl1table.DESC mlog$_tbl1;The query 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, the current user must have the ALTER privilege on the base table to be operated on. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
The SQL 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. Specifies the database where the materialized view is located. Ifdatabase.is omitted, the base table is assumed to be in the database connected by the current session by default.table_name: Specifies the name of the base table corresponding to the materialized view log.alter_mlog_action_list: Indicates the list of modification operations that can be performed on the materialized view log. Multiple operations can be specified, separated by commas (,).
For detailed parameter descriptions of the materialized view log modification syntax, see ALTER MATERIALIZED VIEW LOG.
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_tbl1table.CREATE MATERIALIZED VIEW LOG ON test_tbl1 WITH SEQUENCE(col2, col3, col4) INCLUDING NEW VALUES;Modify the parallelism of the materialized view log on the
test_tbl1table to 5.ALTER MATERIALIZED VIEW LOG ON test_tbl1 PARALLEL 5;Modify the materialized view log on the
test_tbl1table to clean up expired records every1day starting from the current date.ALTER MATERIALIZED VIEW LOG ON test_tbl1 PURGE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;Modify the LOB inline storage length threshold for the materialized view log on the
test_tbl1table.ALTER MATERIALIZED VIEW LOG ON test_tbl1 LOB_INROW_THRESHOLD 10000;
Drop a materialized view log
Considerations
- When dropping a materialized view log, if the base table is involved in an ongoing transaction, the drop operation will be blocked until the transaction completes.
- When a materialized view log is dropped individually, the materialized view does not move to the recycle bin.
Privilege requirements
You must have the DROP TABLE privilege to drop a materialized view log. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
The SQL statement for dropping a materialized view log is as follows:
DROP MATERIALIZED VIEW LOG ON [database.] table;
The parameters are described as follows:
database.: Optional. Specifies the database where the base table of the materialized view log is located. If you omitdatabase, the base table is in your own database by default.table: Specifies the name of the base table corresponding to the materialized view log.
Example:
Drop the materialized view log on table tbl1.
DROP MATERIALIZED VIEW LOG ON tbl1;
Examples
This example shows how to create a regular table, a materialized view log, and an incremental refresh materialized view. It also describes how to drop a materialized view log and an incremental refresh materialized view.
Create table
test_tbl1.CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 INT, col3 INT);Create a materialized view log on table
test_tbl1. Specify to use a sequence number (SEQUENCE) to identify changed data. In the column part, specify the columns to record, includingcol2andcol3.CREATE MATERIALIZED VIEW LOG ON test_tbl1 WITH SEQUENCE (col2, col3) INCLUDING NEW VALUES;Create a materialized view named
mv_test_tbl1. Define the materialized view as incremental refresh with an automatic refresh interval of 5 minutes. In the query part, specify to group by thecol2column from tabletest_tbl1, and calculate the record count (cnt), the count of non-nullcol3records (cnt_col3), and the sum ofcol3(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;View the materialized view log information for table
test_tbl1.SELECT * FROM oceanbase.DBA_MVIEW_LOGS WHERE MASTER = 'test_tbl1';The query 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 setDrop the materialized view log on table
test_tbl1.DROP MATERIALIZED VIEW LOG ON test_tbl1;Drop the materialized view
mv_test_tbl1.DROP MATERIALIZED VIEW mv_test_tbl1;