Complete Refresh Read Consistency Behavior During Refresh and Complete Refresh Performance as Influenced by the ATOMIC_REFRESH Refresh Parameter (Doc ID 553464.1)

Last updated on NOVEMBER 07, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
**Checked for relevance on 30-Jul-2011***


Goal

This note is intended to explain how the following properties are influenced by the ATOMIC_REFRESH parameter of the DBMS_MVIEW.REFRESH, DBMS_MVIEW.REFRESH_ALL_MVIEWS, and DBMS_MVIEW.REFRESH_DEPENDENT procedures:

When a transaction is 'atomic', all parts of that transaction succeed, or none do. Oracle transactions are atomic. Essentially, the ATOMIC_REFRESH parameter for materialized view refresh is meant to control whether each materialized view in a list is refreshed in its own transaction, or whether all materialized views are refreshed together in one transaction to a single point in time. For the latter case, if the refresh fails for any of the materialized views, none of the materialized views are refreshed. However, other behaviors are also affected by this parameter, as discussed below.

Note that we are discussing refreshing individual materialized views here, not refresh groups. When a refresh group is refreshed using DBMS_REFRESH.REFRESH, the refresh is by definition atomic, since each materialized view in the refresh group will be refreshed to the same transactionally consistent point in time in one transaction. However, it is worth noting that the behavior of refresh groups has also changed in 10g and above to maintain atomicity:

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms