Re: Materialized views and redo

  • From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
  • To: Paul.Vincent@xxxxxxxxx
  • Date: Thu, 14 Jun 2007 21:05:26 +0200

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.

Does that mean that you are selecting directly the MV ?

If yes, for a similar scenario, I used the "exchange partition" trick:

create view myview ... -- that's the view statement
create table my_pseudo_mv
nologging pctfree 0
as select * from myview where 1=0;
create index ... on my_pseudo_mv  nologging pctfree 0;
create table my_pseudo_mv_lonely_partition
partition by range (<any column you like>) (partition p_all values
less than (maxvalue))
nologging pctfree 0
as select * from myview where 1=0;
create index ... on my_pseudo_mv_lonely_partition .. nologging pctfree 0 LOCAL;

refresh is simply
truncate the  partition p_all of my_pseudo_mv_lonely_partition
disable indexes on my_pseudo_mv_lonely_partition
insert /*+ append */ into my_pseudo_mv_lonely_partition partition (p_all)
select * from myview -- maybe an ORDER BY may be useful
re-enable indexes on my_pseudo_mv_lonely_partition

alter table my_pseudo_mv_lonely_partition
exchange partition p_all
with table my_pseudo_mv
including indexes without validation;

Almost no redo generated thanks to append into a nologging table,
and rebuilding nologging indexes.

Beauty of this is that if someone is selecting from my_pseudo_mv
while you are exchanging the partition,
the statement will not fail - it would simply continue fetching from
the old segment (my_pseudo_mv_lonely_partition.p_all after the
exchange partition) which is exactly what's mandated by
multiversion read consistency (tested on

Just be careful with dbms_stats - optimally one would collect stats
on p_all before the exchange, unfortunately sometimes the stats are
not propagated by exchange so you may need to collect them
after the exchange on my_pseudo_mv.

Caveat - you are doing DDL on my_pseudo_mv so any package
that references it will be invalidated; that was not an issue for me
but YMMV.


Alberto Dell'Era
"the more you know, the faster you go"

Other related posts: