How to Purge a Large MView Log And Avoid Full Refresh (Doc ID 1539298.1)

Last updated on FEBRUARY 21, 2024

Applies to:

Goal

A Materialized View can be refreshed in two ways:

- Full refresh: This re-evaluates the defining query of the MView and stores the result in the base table underlying the MView.

- Fast refresh: The RDBMS checks MV logs defined on the source tables and only applies the necessary changes. MV logs  keep track of any DML (insert, update, delete) to the source tables). Each row in the MV log describes a single change to the associated source table. These rows are referred to as "change vectors".

Once the required change vectors have been applied to the Materialized View, the RDBMS checks if these change vectors are still required (for example, by another MView). Change vectors not needed for any other MViews are removed from the MV log using a DELETE DML operation.

In order to decide which change vectors are still needed, the RDBMS keeps track of MViews based on each source table's MView Log (the MView is "registered" with the source table instance). At any given time, the RDBMS keeps track of when the last refresh took place. Any change vector younger than this timestamp is considered not obsolete and is kept in the MV log.

Details of MView Log growth and Purging can be reviewed in this document:

Sometimes, a registered MView may be dropped or otherwise inaccessible without having unregistered from the source table instance.

Such events include: network failure during de-registration, dropping the MView schema, decomissioning / removal of the MView instance.

Under these circumstances, an MV Log will grow without limit because change vectors are added to the MV log but never removed.

Growth of the MV log in turn will cause the following operations to become progressively slower:

- Fast Refresh
- Complete Refresh
- De-registering Materialized Views

All of these operations will trigger an MV log scan for query or deletion of change vectors.

This may lead to a situation where availability of MViews is affected.

Simply truncating the Materialized View log may lead to otherwise healthy and up-to-date MViews to loose the capability to FAST REFRESH (due to the mismatch in last refresh timestamps).

As a consequence, costly full refreshes would be required on many (potentially large) MViews, in turn causing delays in availability. Also CPU and network resources may be inadequate to perform this operation in a timely manner.

This article explains how to reduce the size of a large Materialized View log (MV log) and at the same time preserve FAST REFRESH capability of MViews.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.