Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Materialized views and redo

Re: Materialized views and redo

From: Gints Plivna <>
Date: Thu, 14 Jun 2007 13:21:35 +0300
Message-ID: <>

What is your db version?
Default mechanism for complete refresh in 10g is delete/conventional insert, although you can switch back to using dbms_mview.refresh parameter atomic_refresh = false.
For 9i it was truncate/insert/*+append*/

See previous posts on oracle-l as well as asktom thread

Force means that you have fast if it is possible otherwise complete. So it is not clear what method you actually have - fast or complete?

So basically you have two choices
1) refresh with "small" redo (truncate and insert /*+ append */) but have risk that there will be times when your MV wil have 0 rows. 2) refresh with "big" redo and always have full MVs 3) there is also third choice - using 2 alternate MVs with truncate, insert /*+ append */ and use the necessary one. I've explained it here

Gints Plivna

2007/6/14, Paul Vincent <>:
> On a system which otherwise generates very little redo (since about 99% of
> all transactions are read-only, using only SELECTs), we have a materialized
> view which is refreshed once an hour. This was introduced to give a far
> better response time on a common query type which ran in 15 seconds without
> the view, but now runs in a second or less, using the materialized view.
> So far so good... but:
> Every time the materialized view is refreshed, this generates about 40Mb of
> redo activity, which means our archived logfiles area is now growing at a
> rate of around 1Gb per day. For comparison, before the materialized view was
> introduced, we only used to get about 50Mb of newly archived log files per
> day.
> Now, all this redo relates to the refreshing of an object which can easily
> be regenerated by simply refreshing the view. There's no conceivable
> scenario where this redo would ever be needed. So, is there any way of
> completely "switching off" the generation of redo log entries whenever the
> materialized view is auto-refreshed? This would save what's becoming a bit
> of a disk-space headache.
> Paul
> Paul Vincent
> Database Administrator
> Information and Communication Technology
> UCE Birmingham

Received on Thu Jun 14 2007 - 05:21:35 CDT

Original text of this message