Re: Materialized views and redo

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.

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 9.2.0.6).

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.

HTH
Alberto

-- 
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l