Re: Dynamically recreating indexes on materialized views

<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 Received on Wed Mar 08 2006 - 00:05:41 CST