Re: Dynamically recreating indexes on materialized views

On Wed, 8 Mar 2006 16:13:01 +1100, Geoff Muldoon <geoff.muldoon_at_trap.gmail.com> wrote:

>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
>
>

Instead of

• putting the index names in a table with a generic script
• dropping the indexes with a generic script
• creating them afterwards according to the contents of the table

you could

• set the indexes to unusable
• perform an 'alter session set skip_unusable_indexes = true'
• rebuild the indexes with a generic script

Much more robust.

Jaap. Received on Wed Mar 08 2006 - 04:30:29 CST