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: Ajeet Ojha <>
Date: Thu, 14 Jun 2007 14:22:55 +0530
Message-ID: <>

if refresh type is complete then -

oracle - first truncate the mv and then kind of does insert /*+ APPEND */ into the mv.
so the redo will not be generated for table data but redo will be generated for indexes.
one way to minimize it - set the indexes unsuable and then rebuild them after the refresh - now this is assuming that refresh type is complete and there are indexes on the mv in question.

-Oracle DBA

On 6/14/07, Paul Vincent <> wrote:
> 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 - 03:52:55 CDT

Original text of this message