Re: Materialized views and redo

  • From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
  • To: Paul.Vincent@xxxxxxxxx
  • Date: Thu, 14 Jun 2007 13:21:35 +0300

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@xxxxxxxxx>:

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

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


Other related posts: