Re: Materialized views and redo

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.

-Ajeet
-Oracle DBA

On 6/14/07, Paul Vincent <Paul.Vincent_at_uce.ac.uk> 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
> paul.vincent_at_uce.ac.uk
>
>

--
http://www.freelists.org/webpage/oracle-l