Re: Help on Index

In article <7vo6n7$vnl$1_at_nnrp1.deja.com>,   assoy_at_my-deja.com wrote:
> Hi,
>
> I am working on ORACLE Ver 7.3.4 on SunOS (Unix)
> Can anyone tell me when should an Index be rebuilt or Recreate ? Thank
> you for your attention.
>
> Regards
>
> Assoy
>

There are a couple of ways to go about determining when to rebuild an index.

1. Estimate the space requirements for the index based on the table row count to create a rows/K factor then periodically compare the actual space allocated against the calculated need based on this factor and your extent size allocation.
2. Analyze the index (validate structure) to populate index_stats and look at the stats. If the number of deleted leaf rows as a percentage of the leaf rows is too high then the index would benefit from a rebuild. Too high is probably 33% but could be 50% depending on the key pattern (always added to end, reused, etc....)
3. When the index extends a certain number of extents if is time to evaluate if the index needs all the space it is using and should be build with a larger extent size or rebuilt to reclaim space. This depends on your extent management (sizing and fragmentation) policy.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 03 1999 - 10:08:32 CST