RE: Materialized views and redo

Refresh type is FORCE, refresh mode is PERIODIC. There are 3 indexes on
the MV, but setting them unusable before the refresh and rebuilding
after the refresh would be impractical, since this is an hourly refresh.
 

Paul Vincent
Database Administrator
Information and Communication Technology
UCE Birmingham
paul.vincent@xxxxxxxxx 

 


________________________________

        From: Ajeet Ojha [mailto:ajeeto@xxxxxxxxx] 
        Sent: 14 June 2007 09:53
        To: Paul.Vincent@xxxxxxxxx
        Cc: Oracle-L@xxxxxxxxxxxxx
        Subject: 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@xxxxxxxxx> 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@xxxxxxxxx

Other related posts: