A Materialized View Log (mlog) is used to record incremental update data of the user table (base table). This supports the quick refresh feature of materialized views. 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 for user tables.
A base table is bound to only one materialized view log.
If a transaction is running when you create a materialized view log, the creation operation will be blocked until the transaction is completed.
The materialized view log does not support
LOBcolumns.The materialized view log does not support the following data types: JSON, XML, GIS, and UDT.
Generation columns (including virtual and non-virtual columns) are not supported in materialized view logs.
The materialized view log does not support specified partitions and has a partitioning binding relationship with the base table.
The name of a materialized view log can be at most 64 characters long, the same as that of a regular table. However, the name of the base table of a materialized view log, together with the
mlog$_prefix, cannot exceed 64 characters.The SYSLOG method does not support table-level recovery.
The physicalized materialized view log is not moved to the recycle bin when it is deleted individually.
The materialized view log cannot be modified after it is created.
Do not create indexes on the materialized view log.
DML operations are not supported for the materialized view log. An error will be returned when you perform DML operations on a materialized view log.
Privilege required
- To create a materialized view log, you must have the
SELECTprivilege on the base table and theCREATE TABLEprivilege. - To drop a materialized view log, you must have the
DROP TABLEprivilege. - Only the
SELECTprivilege is supported on materialized view logs. Other DML operations are not supported.
Define a materialized view log schema
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 Name | Type | Description |
|---|---|---|
| sequence$$ | in64_t | The auto-increment column, which is the primary key column for materialized view logs (mlogs).
NoteThe primary key of an mlog is the combination of the primary keys of the base table, all partitioning keys (if any), and the |
| primary key | follows the base table | If the base table has a primary key, the mlog records the primary key columns of the base table. If the primary key is composite, multiple columns are recorded. |
| dmltype$$ | char(1) | Records the DML type. It can be I, D, or U, representing INSERT, DELETE, and UPDATE respectively. |
| old_new$$ | char(1) | Marks the old and new values in UPDATE statements. An UPDATE operation writes two rows to the materialized view log, one for the old value before the UPDATE operation and one 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. | A regular column of the base table. |
| ... | N/A | N/A |
| column N | Follows base table. | The Nth normal column in the base table. |
| ora_rowscn | N/A | A rowid is stored in a hidden column in the storage layer and can be read. |
| m_row$$ | uint64_t | This column is recorded in the mlog file only when the base table does not have a primary key. The mlog file must contain the primary key column of the base table. If the base table does not have a primary key, the hidden primary key of the base table is named M_ROW$$ in the mlog file. |
Manage existing materialized view logs
- You can directly query the schema where the materialized view log resides for its structure and the data in it.
- 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 the materialized view log grows beyond the available disk space, an error will be returned. In this case, you must drop the materialized view log and recreate it before you can use it again.
Impact of operations on materialized view logs
Base table DML operations
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:
- When you execute an
INSERTstatement on a base table, a record is inserted into the materialized view log for each inserted row. Thedmltype$$column value of these records isI, and theold_new$$column value isN. - If you execute a
DELETEoperation on a base table, each deleted row is inserted into the materialized view log as a record. Thedmltype$$column value of this record isD, and theold_new$$column value isO. - If you perform an
UPDATEoperation on a base table, each modified row will generate two records in the materialized view log. The first record contains the original value of theUPDATEd row, with thedmltype$$column value beingUand theold_new$$column value beingO. The second record contains the new value after theUPDATE, with thedmltype$$column value beingUand theold_new$$column value beingN.
DDL operations on base tables
Before you drop the base table, you must drop the corresponding materialized view log first, otherwise an error will be reported. The materialized view log is bound to the base table, so you cannot drop the base table and retain the materialized view log.
For more information about DDL operations supported by a base table, see Online DDL operations and offline DDL operations.