A Materialized View Log (mlog) records the incremental update data of the user table (base table) to support the quick refresh feature of the materialized view. The mlog is a record table that tracks changes in the base table and applies these changes to the corresponding materialized view, enabling quick refresh.
Limitations
Materialized view logs can be created only on regular user tables.
A base table is bound to only one materialized view log.
When you create a materialized view log, if the base table has an ongoing transaction, the operation is blocked until the transaction is completed.
The materialized view log does not support
LOBcolumns.Data of the following four types is not supported in materialized view logs: JSON, XML, GIS, and user-defined type (UDT).
Generation columns (including virtual and non-virtual columns) are not supported in materialized view logs.
The materialized view log does not support specifying partitions, and its partitions are bound to those of the base table.
The name of a materialized view log cannot be longer than 64 characters, the same as that of a regular table. However, the base table name of a materialized view log, with the
mlog$_prefix included, cannot exceed 58 characters.Log compression is not supported for table-level restore.
A materialized view log that is deleted individually does not go to the recycle bin.
The materialized view log cannot be modified after it is created.
An index cannot be created on a materialized view log.
DML operations on the materialized view log are not supported, and an error will be returned when you perform DML operations on the materialized view log.
Permissions
- To create a materialized view log, you need the
SELECTprivilege on the base table and theCREATE TABLEprivilege. - Dropping a materialized view log requires the
DROP TABLEprivilege. - A materialized view log can be granted only the
SELECTprivilege. It does not support other DML operations.
Schema definition of materialized view log schemas
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:
| Column | Type | Description |
|---|---|---|
| sequence$$ | in64_t | The auto-increment column, which is the primary key column of the materialized view log (mlog).
NoteThe primary key of the mlog consists of the primary key of the base table, all partitioning keys, if any, and the auto-increment column |
| primary key | follows base table | The mlogs record the primary key columns of a base table. If the base table has a composite primary key, the columns are recorded. |
| dmltype$$ | char(1) | Records the type of DML. It can be I, D, or U, representing INSERT, DELETE, and UPDATE respectively. |
| old_new$$ | char(1) | Indicates whether the value in the row is the original value or the new value in an UPDATE statement. During an UPDATE operation on a row, two rows are written to the materialized view log, one for the original value and the other for the new value, which are marked with O and N, respectively. |
| column 1 | Base table to which the column belongs. | Normal column of the base table. |
| ... | N/A | N/A |
| column N | follower | The Nth ordinary column of the base table. |
| ora_rowscn | N/A | A pseudocolumn that records a hidden column in the storage layer and is readable. |
| m_row$$ | uint64_t | This column is recorded in the mlog only if the base table has no primary key. The mlog must contain the primary key column of the base table. If the base table has no primary key, the name of the hidden primary key of the base table is recorded as M_ROW$$ in the mlog. |
Manage existing materialized view logs
- You can directly query the schema where the materialized view log is located, as well as the data in the schema.
- You can call DBMS_MVIEW.PURGE_LOG(table_name) to purge the materialized view log of a base table.
- If the size of the materialized view log grows beyond the available disk space, an error will be returned. In this case, you must drop and recreate the materialized view log to continue.
Operations on base tables and 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, all INSERT, DELETE, and UPDATE operations on the base table are recorded in the materialized view log, as shown below:
- If you execute an
INSERToperation on a base table, each inserted row generates a record in the materialized view log. Thedmltype$$column value of this record isI, and theold_new$$column value isN. - When you execute the
DELETEstatement on a base table, a record is inserted into the materialized view log for each deleted row. In this record, the value of thedmltype$$column isD, and the value of theold_new$$column isO. - If you perform an
UPDATEoperation on the base table, each modified row will generate two records that are inserted into the materialized view log. The first record contains the old value of theUPDATEd row, with thedmltype$$column value set toUand theold_new$$column value set toO. The second record contains the new value after theUPDATE, with thedmltype$$column value set toUand theold_new$$column value set toN.
Base table DDL operations
You need to drop the corresponding materialized view log before you drop the base table, because the materialized view log is bound to the base table.
For more information about DDL operations supported by base tables, see Online DDL operations and Offline DDL operations.