Dynamically recreating indexes on materialized views

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      Received on Tue Mar 07 2006 - 23:13:01 CST