Re: Materialized views and redo

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
http://www.freelists.org/archives/oracle-l/01-2007/msg00666.html as well as asktom thread
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15695764787749

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 http://www.gplivna.eu/papers/mat_views_search.htm

Gints Plivna
http://www.gplivna.eu

2007/6/14, Paul Vincent <Paul.Vincent_at_uce.ac.uk>:
>
> 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