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