Re: Dynamically recreating indexes on materialized views

"Geoff Muldoon" <geoff.muldoon_at_trap.gmail.com> wrote in message news:MPG.1e7912e937a8e9398977f_at_news.readfreenews.net...
> Hi all,
>
> On a pair of separate Linux boxes, instances or 9.2 (prod) and 10g (dev),
> would prefer a solution that could work on both versions.
>
> I use a cron'd PL/SQL procedure which calls DBMS_MVIEW to refresh a series
> of materialized views each night - complete refresh needed on most as
> there is aggregation in the MV sources.
>
> 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.
>
> I would like to use a process which will allow for changes to the indexing
> strategy without the need for any re coding.
>
> Pseudo-code something like:
>
> cursor mvs is
> <ordered list of materialized views to be refreshed>
> cursor mv_indexes is
> <select index names from user_indexes for current mv>
> cursor mv_index_ddls is
> <select DDL text from temp DDL table for indexes for current mv>
> begin
> <for mv in mvs loop>
> -- load DDLs for index for this mv into temp table
> -- using select DBMS_METADATA.GET_DDL
> <load the values into the temp DDL table for this mv>
> -- drop all the indexes on this mv
> <for mvi in mv_indexes loop>
> <exec immediate drop the index>
> <end loop>
> <DBMS_MVIEW.REFRESH(current mv, 'C')>
> -- recreate the indexes on current mv
> <for mvid in mv_index_ddls loop>
> <exec immediate create the index>
> <end loop>
> <end loop>
> end;
>
> Is this the recommended way to go, or am I way off track?
>
> Geoff M
>
>

You could just do an index rebuild, much faster.

Jim Received on Tue Mar 07 2006 - 23:46:37 CST