Tuesday, 7 August 2007

Using atomic_refresh Parameter in Oracle Materialzed View Refresh

I was doing a refresh today that involves materialised views from the second schema and a colleague asked me this question. I thought I should share oracle's answer to it.

Why use atomic_refresh=>FALSE?

SQL>execute dbms_mview.refresh('schema.mv_name','C',atomic_refresh=>FALSE);

"Refreshing single MV is not atomic even if ATOMIC_REFRESH = true (the default). This can lead to wrong results in sessions querying against the materialized view as the row count can suddenly change to 0 (as the refresh truncates the MV). An atomic refresh should not affect read consistency in this way"

In 10g a Complete Refresh of single Materialsed View will do a delete instead of truncate. To force the refresh to do a truncate instead of a delete parameter atomic_refresh must be set to false.

That means that since it will be doing truncate instead of delete, the refresh will go faster.

"The dafault value for atomic_refresh in dbms_mview.refresh in 8.1.7, 9i and 10g is TRUE, but in 8i and 9i Oracle does not check the atomic parameter, but because of some enhancements it will do this in 10g"

No comments: