Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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>
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
![]() |
![]() |