Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: fragmentation and index maintainence

From: DiggidyMack69 <DiggidyMack69_at_hotmail.com>
Date: 18 May 2004 08:05:00 -0700
Message-ID: <c86ce4f.0405180705.6bc45f56@posting.google.com>


"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<4092660b$0$4543$afc38c87_at_news.optusnet.com.au>...
> 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!
>
> Regards
> HJR
Thanks for the info. For the record I was talking about index fragmentation. It has been told to me that some of my indexes are 'fragmented' by an 'authority'. That is all. I didn't have any stats or anything to back-up or disregard that claim. Thanks to folks here I have alot more useful information. I assume that some automated tool has been run to generate a report of 'fragmented' indexes.

DM Received on Tue May 18 2004 - 10:05:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US