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

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:

• Read Consistency: Whether or not the data in a materialized view still can be queried while a complete refresh is under way, or return zero rows.
• Performance: Speed of a complete refresh.
• Transaction Boundaries: Where or not a group of materialized views are refreshed in a single transaction, or in separate transactions.

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:

• In 9.2, using DBMS_REFRESH.REFRESH to refresh a refresh group that contains only one complete refresh materialized view automatically results in truncation of the materialized view prior to inserting the rows back in. However, if there is more than one materialized view in the refresh group, a row-by-row delete from each materialized view will instead be done. Therefore, if there is one materialized view in the refresh group the complete refresh will be faster, but queries against the materialized view will return zero rows; if there are two or more materialized views in the refresh group the complete refresh will be slower but queries against the materialized views in the refresh group will always return rows. Also, of course, if there are two or more materialized views in the refresh group, these materialized views will be refreshed to the same transactionally consistent point in time in one transaction. For this reason in 9.2 a 'dummy' materialized view containing no data is sometimes added to a refresh group containing a single materialized view.
• In 10.2, this behavior has become consistent to enforce the meaning of atomicity. Even if there is only one materialized view in a refresh group, row-by-row deletes are always done during complete refresh of the refresh group. This ensures read consistency against the materialized views in the refresh group during the refresh.

Solution

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