Re: fragmentation and index maintainence

"DiggidyMack69" <DiggidyMack69_at_hotmail.com> wrote in message news:c86ce4f.0404300601.269e175d_at_posting.google.com...
> 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?

Hi

Don't get me started on indexes ...

index_stats is only populated with details of the last index which has been analyzed with the VALIDATE STRUCTURE clause.

Probably the best rule of thumb is not to worry about indexes fragmentation and focus on other things instead.

You may find a presentation of mine that I did recently for our local user group useful (check download section at www.actoug.org.au). The accompanying white paper that discusses things in much more detail is coming out soon.

Hopefully if you get through it all, you'll find out that index fragmentation is rarely an issue.

Cheers

Richard Received on Fri Apr 30 2004 - 09:20:18 CDT