Oracle Materialized View Log

Materialized View Logs in Oracle

A materialized view log is required on a master if we want to fast refresh materialized views based on the master. When we create a materialized view log for a master table or master materialized view, Oracle creates an underlying table as the materialized view log. A Mview log can hold the primary keys, rowids, or object ids of rows, or both, that have been updated in the master table or master materialized view. A materialized view log can also contain other columns to support fast refreshes of materialized views with subqueries.

CREATE MATERIALIZED VIEW LOG ON [schema.]table_name

[... storage options ...]

[... table_partitioning_clause ...]

[WITH {OBJECT | PRIMARY KEY | ROWID | SEQUENCE | column_list}]

[{INCLUDING | EXCLUDING} NEW VALUES];

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

The name of a materialized view log's table is MLOG$_master_name. The materialized view log is created in the same schema as the target master. One materialized view log can support multiple materialized views on its master table or master materialized view.

When changes are made to the master table or master materialized view using DML, an internal trigger records information about the affected rows in the materialized view log. This information includes the values of the primary key, rowid, or object id, or both, as well as the values of the other columns logged in the materialized view log. This is an internal AFTER ROW trigger that is automatically activated when we create a materialized view log for the target master table or master materialized view. It inserts a row into the materialized view log whenever an INSERT, UPDATE, or DELETE statement modifies the table's data. This trigger is always the last trigger to fire.

SQL> CREATE MATERIALIZED VIEW LOG ON emp WITH SEQUENCE, ROWID INCLUDING NEW VALUES;

Following are the types of materialized view logs:

• Primary Key: The materialized view records changes to the master table or master materialized view based on the primary key of the affected rows.
• Row ID: The materialized view records changes to the master table or master materialized view based on the ROWID of the affected rows.
• Object ID: The materialized view records changes to the master object table or master object materialized view based on the object identifier (ID) of the affected row objects.
• Combination: The materialized view records changes to the master table or master materialized view based any combination of the three options. It is possible to record changes based on the primary key, the ROWID and the object ID of the affected rows. Such a materialized view log supports primary key, ROWID and object materialized views, which is helpful for environments that have all three types of materialized views based on a master.

A combination materialized view log works in the same manner as a materialized view log that tracks only one type of value, except that more than one type of value is recorded. For example, a combination materialized view log can track both the primary key and the rowid of the affected row are recorded.

Though the difference between materialized view logs based on primary keys and rowids is small (one records affected rows using the primary key, while the other records affected rows using the physical rowid), the practical impact is large. Using rowid materialized views and materialized view logs makes reorganizing and truncating your master tables difficult because it prevents your ROWID materialized views from being fast refreshed. If we reorganize or truncate your master table, then your rowid materialized view must be COMPLETE refreshed because the rowids of the master table have changed.

SQL> CREATE MATERIALIZED VIEW LOG ON cust

TABLESPACE mv_logs STORAGE(INITIAL 1M NEXT 1M) WITH ROWID;

Updatable Materialized View Log

An updatable materialized view log (USLOG$_materialized_view_name), in Oracle database, is used to determine which rows must be overwritten or removed from a materialized view during a fast refresh. A read-only materialized view does not create this log, and Oracle does not use this log during a complete refresh because, the entire materialized view is replaced.

If there is a conflict between an updatable M-view and a master, then, during a refresh, the conflict may result in an entry in the updatable materialized view log that is not in the materialized view log at the master site or master materialized view site. In this case, Oracle uses the updatable materialized view log to remove or overwrite the row in the materialized view.

The updatable materialized view log is also used when we fast refresh a writeable materialized view, as illustrated in the following scenario:

1. A user inserts a row into a writeable materialized view that has a remote master. Because the materialized view is writeable and not updatable, the transaction is not stored in the deferred transaction queue at the materialized view site.
2. Oracle logs information about this insert in the updatable materialized view log.
3. The user fast refreshes the materialized view.
4. Oracle uses the information in the updatable materialized view log and deletes the inserted row. A materialized view must be an exact copy of the master when the fast refresh is complete. Therefore, Oracle must delete the inserted row.

Internal Trigger for the Updatable Materialized View Log

Like the internal trigger at the master site or master materialized view site, an internal trigger at the materialized view site records DML changes applied to an updatable materialized view in the USLOG$_materialized_view_name log. A read-only materialized view does not create this trigger.

Restriction on import of MViews and MView Logs to different Schema

Materialized views and materialized view logs are exported with the schema name explicitly given in the DDL statements. Therefore, materialized views and materialized view logs cannot be imported into a schema that is different than the schema from which they were exported. If we attempt to use the FROM USER and TO USER import options to import an export dumpfile that contains materialized views or materialized view logs, then an error will be written to the import log file and the items will not be imported.


Related Oracle Articles: Materialized Views   Materialized View Types  Materialized Views Refresh Groups