Skip to Main Content
  • Questions
  • Materialized View Fast Refresh and the ATOMIC_REFRESH Parameter

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajeeva.

Asked: February 15, 2018 - 7:13 pm UTC

Last updated: February 16, 2018 - 6:06 am UTC

Version: RDBMS 12.1.0.2

Viewed 1000+ times

You Asked

Hi Tom,

I have about 25 MV's in my production application. Mostly in two refresh groups. Very few stand alone.
One group (lets call it GROUP-A) refreshes every minute as a business requirement and the other (GRIOUP-B) every hour. Few more MV's every 24 hours.

Environment is a 2 node RAC data guarded with another 2 node RAC.

Ever since my developer created these MV's (and the refresh groups) the redo log generation has increased. It generates between 5GB to 13GB of redo on average per hour.

I was wondering by using the ATOMIC_REFRESH=FALSE (So it truncates rather than delete) in conjunction with FAST REFRESH. Is this combination possible?

Thanks in advance.

and Connor said...

No its not - the two achieve differing goals.

Fast Refresh is "fast" because we

- capture *changes* to the source data
- apply only those deltas to the materialized view

Performing a truncate obviously invalidates that approach.

However, if you have huge amounts of redo - it would suggest that the amount of changes being applied is actually massive. In which case, it may well be the case that a Complete refresh would actually perform faster. If this indeed the case (you can run a benchmark to test this), then examining the cost/benefit of doing the refreshes atomically comes back onto the table. (no pun intended)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.