Home > Uncategorized > Materialized View Refresh

Materialized View Refresh

Check refresh mode, refresh method and last refresh time from USER_MVIEWS:

SELECT mview_name, refresh_mode, refresh_method,
 last_refresh_type, last_refresh_date
 FROM user_mviews;

Change of refresh behaviour in 10g

Upgrading from Oracle 9i to Oracle 10g will change the MV refresh behaviour. Oracle 10g will use the DELETE command to remove rows and a normal INSERT to repopulate it. In Oracle 9i and earlier releases, Oracle did a TRUNCATE and INSERT /*+APPEND*/, which is more efficient, but had the side effect that users will see no rows while the refresh is taking place.

If you prefer the older truncate/append behaviour, change the refresh method to set atomic_refresh = false. Here is an example:

 -- use this with 10g/11g to return to truncate/append behavior
 dbms_mview.refresh('MY_TEST_MV', method=>'C', atomic_refresh=>false);


Atomic_refresh parameter is to allow either all of the tasks of a transaction are performed or none of them (Atomicity). If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction.If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views.

If you set that to FALSE, it’ll do a truncate + insert /*+ append */ on a FULL refresh.

When atomic_refresh=>true, Oracle performs deleting from MView table.
When atomic_refresh=>false, Oracle truncates the MView table.

Atomic refresh does a

a) truncate (data disappears right away, poof)
b) insert /*+ APPEND */ – direct path load, which maintains indexes
c) commits (data reappears)

The indexes won’t/don’t go unusable, but the materialized view “disappears” for the duration of the refresh.

Source / Reference Links:

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: