Databases Reference
In-Depth Information
Managing MV Logs
MV logs are required for fast refreshable MVs. The MV log is a table that stores DML information for a master (base)
table. The MV log is created in the same database as the master table with the same user that owns the master table.
You need the
CREATE TABLE
privilege to create an MV log.
The MV log is populated by an Oracle internal trigger (that you have no control over). This internal trigger inserts
a row into the MV log after an
INSERT
,
UPDATE
, or
DELETE
on the master table. You can view the internal triggers in use
by querying
DBA/ALL/USER_INTERNAL_TRIGGERS
.
An MV log is associated with only one table, and each master table can have only one MV log defined for it. You
can create an MV log on a table or on another MV. Multiple fast refreshable MVs can use one MV log.
After an MV performs a fast refresh, any records in the MV log that are no longer needed are deleted. In the event
that multiple MVs are using one MV log, then records are purged from the MV log only once they aren't required by
any of the fast refreshable MVs.
Table
15-3
defines the terms used with MV logs. These terms are referred to in the following sections in this
chapter that relate to MV logs.
Table 15-3.
MV Log Terminology and Features
Term
Meaning
Materialized view (MV) log
Database object that tracks DML changes to MV base table; required for
fast refreshes
Primary key MV log
MV log that uses the base table primary key to track DML changes
ROWID
MV log
MV log that uses the base table
ROWID
to track DML changes
Commit SCN MV log
MV log based on the commit SCN instead of a timestamp; available in Oracle
Database 11g Release 2 and higher
Object ID
Object identifier used to track DML changes
Filter column
Nonprimary key column referenced by an MV subquery; required for some fast
refresh scenarios
Join column
Nonprimary key column that defines a join in the subquery
WHERE
clause;
required for some fast refresh scenarios
Sequence
Sequence value required for some fast refresh scenarios
New values
Specifies that old and new values be recorded in the MV log; required for
single-table aggregate views to be eligible for fast refresh
Creating an MV Log
Fast refreshable views require an MV log to be created on the master (base) table. Use the
CREATE MATERIALIZED VIEW
LOG
command to create an MV log. This example creates an MV log on the
SALES
table, specifying that the primary key
should be used to identify rows in the MV log:
SQL> create materialized view log on sales with primary key;
You can also specify storage information, such as the tablespace name:
create materialized view log on sales
pctfree 5
