Re: Maintenance on Indexes

The simplest way is to generate a script to rebuild all the indexes, assuming you are on 7.3 or above. Try this:

SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD;' FROM DBA_INDEXES WHERE owner NOT IN ('SYS','SYSTEM');

If you spool this to a file, then you will have a script that you can run to rebuild all indexes. Note that you do not have to drop any PK's, UK's, or FK's to do it this way. ALTER INDEX...REBUILD uses the existing index as the input rather than the actual table, so you don't have to drop any constraints. This method also goes much faster than dropping and recreating. Users can be doing SELECTs while this occurs, but cannot be doing INSERT/UPDATE/DELETE activity.

Good luck,
Dave

Gillian wrote in message <350E980F.6E456F8B_at_entergy.com>...
>I need to cut down the fragmentation on the indexes in Financials.
>
>Is there a statement I can use to DROP and CREATE all simultaneously.
>There are about
>12526 indexes. I could probably use the DBA_IND_COLUMNS table together
>with the
>DBA_INDEXES table.
>
>Let me know.
>Much appreciated.
>
>Gillian
>zmkj04_at_hotmail.com
>
Received on Fri Mar 20 1998 - 00:00:00 CST