"Geoff Muldoon" <geoff.muldoon_at_trap.gmail.com> wrote in message
news:MPG.1e791f411b292b9c989780_at_news.readfreenews.net...
> <jim dot scuba dot kennedy at gee male dot com> says...
> >
> > "Geoff Muldoon" <geoff.muldoon_at_trap.gmail.com> wrote in message
> > >
> > > I am convinced that I will (eventually as data size grows, if not
already
> > > on some) need to drop all the indexes on these materialized views,
then
> > > refresh them, then recreate the indexes.
> >
> > You could just do an index rebuild, much faster.
>
> No, don't think so.
>
> The problem is the speed of the DBMS_MVIEW.REFRESH with the indexes in
> place.
>
> Example - one of the bigger materialized views (2 million records with 12
> indexes):
>
> 9.2 prod version with no indexes refreshes in 10 min, with indexes in
> place takes 30 min, plus a only then couple of min to recalc index stats
> afterwards.
>
> 10g dev version with no indexes refreshes in 7 min, with indexes takes 90
> min to bork out running out of undo tablespace (still no totally certain
> why, suspect it is to do with different degree of parallelism on index
> rebuilds).
>
> Geoff M
Then mark the indexes unusable, alter session to allow skipping of unusable
indexes and then rebuild the indexes.
Jim
Received on Wed Mar 08 2006 - 22:02:18 CST
