Re: Analyzing indexes

Chuck wrote:
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in
> news:pan.2003.03.11.19.53.03.636123_at_yahoo.com.au:
>
> > On Tue, 11 Mar 2003 14:38:05 +0000, Chuck wrote:
> >
> >> I need to determine whether or not a couple of indexes need to be
> >> rebuilt.
> >
> > They almost certainly won't be!
> >
> > What makes you think they might need it? If this is an index on a
> > monotonically incrementing sequence number, and you've been doing
> > sporadic deletes of table records with low sequence numbers, then yes,
> > you might have a need.
>
> Just the fact that I reorged them once last summer and saw dramatic
> performance increases on queries involving the indexes in question. That
> was the first reorg in 18 months. These are indexes on sequential numbers
> and yes we bulk purge older rows (ie. lower sequence #'s) from the
> indexed table.

Assuming the blocks from the older purged rows are totally freed up (ie you're are not leaving sporadic old rows around) then these blocks will be reused for the subsequent high value sequence numbers thus avoiding the rebuild requirement.

Whilst rebuilding might improve query speed by "packing" the blocks better, you might also find it has a detrimental effect on dml performance and possibly an increased drain on your redo.

Also, you might like to consider using coalesce.

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"