Did You Know #6 – MViews

When using materialized views, refresh is an important method we use. We can perform a full or fast refresh, depending on the materialized view properties and the existance of a materialized view log.

One thing that is important to know about refresh is that it is a transactional process. This means that all of the refreshed materialize views will be refreshed in a single transaction, and it will happen without any user interferance.
When performing a complete refresh, Oracle will use the “delete” command to remove all data from the materialized views and then will refresh them. After the refresh is completed, Oracle will commit and allow everyone to see the refreshed data. This is the default.
In some cases, we prefer Oracle to use “truncate” to empty the materialized views. It might interfere queries and every materialized view will be refreshed individually, but it is much faster with less redo generation.
This can be done by specifying the “atomic_refresh” parameter in “dbms_mview.refresh” to false.

exec dbms_mview.refresh('MY_MVIEW', 'C', atomic_refresh=> false).

Hope you find this helpful,

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s