Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: fragmentation and index maintainence

Re: fragmentation and index maintainence

From: Howard J. Rogers <>
Date: Sat, 01 May 2004 00:43:29 +1000
Message-ID: <4092660b$0$4543$>

DiggidyMack69 wrote:

> Hello folks,
> I am a longtime DB developer but have been doing alot more DBA (Oracle
> 8i) type responsabilities lately. One thing I am somewhat struggling
> on is the maintainence of indices. I have read many posts on
> fragmentation, most of which tell you to look in the data dictionary,
> and have been running queries against several of the system views
> including dba_indexes and index_stats.
> I'm finding it strange that index_stats is empty even though I have
> computed stats for most application tables??
> What are good rules of thumb to look at in regards to when looking for
> signs of fragmentation and other problems of the like?

A good rule of thumb?

Don't bother.

Seriously. If by fragmentation you mean tablespace fragmentation, then just use consistent extent sizes for everything within a tablespace, and you won't actually *get* any fragmentation. Better still, since you're on 8i, use locally managed tablespace with a uniform extent size clause and you'll never have to worry about fragmentation again.

If you mean index fragmentation, then don't bother either, because 99.99% of indexes on the planet are quite capable of looking after their own space needs when left entirely to their own devices. I smell a suggestion that you want to rebuild your indexes to compact them: please don't go down that route. An index on a monotonically incrementing sequence number that is subject to sporadic deletes might, occasionally and just possibly, if the wind is in the right direction and it's a full moon, benefit from a rebuild. But an index on a monotonically incrementing sequence number that is subject to bulk deletes won't need one, and indexes not on monotonically incrementing sequence numbers won't need one either. Given enough time and sufficient subsequent DML, space vacated by old index entries will quite happily be automatically re-used by new ones.

Index_stats is a statistics view on indexes which has to be populated by a different mechanism than the DBA_INDEXES view. For the latter, you'd do analyze index X compute statistics, but for the former you have to do analyze index X validate structure. The view only contains the statistics for the last index validated, so don't go doing batch validates and expect to cut corners!

HJR Received on Fri Apr 30 2004 - 09:43:29 CDT

Original text of this message