A materialized view log (mlog) records the incremental data updates of a user table, that is the base table of the materialized view, to support quick refresh of the materialized view. An mlog is a record table that tracks changes to the base table and applies these changes to the corresponding materialized view to achieve quick refresh of the materialized view.
Limitations
You can create a materialized view log only on a normal user table.
You can bind a base table to only one materialized view log.
When you create a materialized view log, if a transaction is running on the base table, the materialized view log create operation is blocked until the transaction is completed.
Materialized view logs do not support
LOBcolumns.Materialized view logs do not support the JSON, XML, GIS, or UDT data type.
Materialized view logs do not support generated columns, including virtual columns and non-virtual columns.
Materialized view logs do not support partitioning.
The name of a materialized view log cannot exceed 64 characters in length, which is the same as that of a normal table. In addition, the name of a materialized view log consists of a prefix
mlog$_and the name of the base table. Therefore, if you want to create a materialized view log on a table, the table name cannot exceed 58 characters.Materialized view logs do not support table-level restore.
Materialized view logs that are separately dropped are not moved to the recycle bin.
Materialized view logs do not support the
ALTERoperation.You cannot create indexes on materialized view logs.
Materialized view logs do not support DML operations.
User privileges
- You need the
SELECTprivilege on the base table and theCREATE TABLEprivilege to create a materialized view log. - You need the
DROP TABLEprivilege to drop a materialized view log. - A materialized view log only supports the
SELECTprivilege. Other DML operations are not supported.
Schema definition of a materialized view log
You can create only one materialized view log for a table. The schema name of the materialized view log is mlog$_table, in which table is the name of the base table.
The following table describes the schema definition of a materialized view log.
| Column | Type | Description |
|---|---|---|
| m_row$$ | uint64_t | If the base table does not have a primary key, the rowid column is used as the hidden primary key of the base table. |
| sequence$$ | in64_t | The update sequence of multiple rows in a transaction. The sequence is maintained at the SQL layer, obtained from trans_desc, and then written to the storage layer. |
| dmltype$$ | char(1) | The operation type. Valid values: I, D and U, which indicates INSERT, DELETE, and UPDATE respectively. |
| old_new$$ | char(1) | Indicates whether the value is an old or new value in an UPDATE statement. For each UPDATE operation on one data row in the base table, the system writes two rows of data to the materialized view log. The row with the old value is marked with O. The row with the new value is marked with N. |
| primary key | The same as the base table | The primary key of the base table. If the base table has a composite primary key, the value contains multiple columns. |
| column 1 | The same as the base table | The normal column 1 in the base table. |
| ... | N/A | N/A |
| column N | The same as the base table | The normal column N in the base table. |
| ora_rowscn | N/A | The pseudocolumn, which is recorded in a hidden column at the storage layer and can be read. |
Use an existing materialized view log
- You can directly query the schema and data of a materialized view log.
- You can use the
DBMS_MVIEW.PURGE_LOG(table_name)statement to purge a materialized view log. - When the size of a materialized view log exceeds the available disk space, an error is reported. In this case, you must drop the materialized view log and create a new one.
Impact of base table operations on the materialized view log
DML operations on the base table
The materialized view log of a base table records the following DML operations on the base table: INSERT, DELETE, and UPDATE.
- If you perform an
INSERToperation on the base table, the system inserts a record to the materialized view log for each inserted row of data. In the record, thedmltype$$column value isI, and theold_new$$column value isN. - If you perform a
DELETEoperation on the base table, the system inserts a record to the materialized view log for each deleted row of data. In the record, thedmltype$$column value isD, and theold_new$$column value isO. - If you perform an
UPDATEoperation on the base table, the system inserts two records to the materialized view log for each modified row of data. In the first record, the old value before theUPDATEoperation is recorded, thedmltype$$column value isU, and theold_new$$column value isO. In the second record, the new value after theUPDATEoperation is recorded, thedmltype$$column value isU, and theold_new$$column value isN.
DDL operations on the base table
Before you drop a base table, you need to drop its materialized view log first. Otherwise, an error is reported. This is because the materialized view log is bound to the base table.