CREATE MATERIALIZED VIEW LOG

The CREATE MATERIALIZED VIEW LOG statement creates a log in which changes to the detail table are recorded. The log is required for an asynchronous materialized view that is refreshed incrementally. The log must be created before the materialized view is created. The log is a table in the user's schema called MVLOG$_detailTableID, where detailTableID is a system-generated ID.

This statement also creates other objects for internal use:

A global temporary table called MVLGT$_detailTableID
A sequence called MVSEQ$_detailTableID

The objects are dropped when the DROP MATERIALIZED VIEW LOG statement is executed.

Required privileges

SELECT on the detail table and

CREATE TABLE or CREATE ANY TABLE (if not owner)

SQL syntax

CREATE MATERIALIZED VIEW LOG ON tableName
  [WITH [PRIMARY KEY][,ROWID]|[ROWID][,PRIMARY KEY] [(columnName[,...])]]

Parameters

Parameter	Description
`tableName`	Name of the detail table for the materialized view
`[(columnName[,...])`	List of columns for which changes will be recorded in the log.

Description

Use the WITH clause to indicate the keys and columns for which changes will be recorded in the materialized view log.
Specify the PRIMARY KEY option to record changes in the primary key columns. It is the default if the WITH clause is omitted or ROWID is not specified.
Specify the ROWID option to record the rowid of all changed rows. The ROWID option is useful when the table does not have a primary key or when you do not want to use the primary key when you create the materialized view.
You can specify both PRIMARY KEY and ROWID. The materialized view log may be used by more than one asynchronous materialized view using the specified table as the detail table.
Only one materialized view log is created for a table, even if the table is the detail table for more than one materialized view with FAST refreshes. Make sure to include all the columns that are used in different asynchronous materialized views with FAST refresh.
A materialized view log cannot be created using a materialized view as the table or for tables in cache groups.
A materialized view log cannot be altered to add or drop columns.
You can specify only one PRIMARY KEY clause, one ROWID clause and one column list for a materialized view log.
You cannot include the primary key columns in the column list when you specify the PRIMARY KEY option.

Examples

Create a materialized view log on the employees table. Include employee_id (the primary key) and email in the log.

CREATE MATERIALIZED VIEW LOG ON employees WITH PRIMARY KEY (email);