Re: more index questions

<bplegend_at_yahoo.com> wrote in message
news:5d0822ae.0106201914.5ca8894_at_posting.google.com...
> You rebuild an index when your index has about 5 to 10% fragmented, a
> number of chained/migrated rows and after a large load of data.
>

Close, but no banana. That sounds like 'when do I reorganise my tables', not rebuild indexes. Indexes don't suffer from row migration.

> You can look at your 'analyze index' output and decide whether the
> index needs a rebuild.
>

Mmmm. Not terribly helpful, really.

Tell you what: put it in numbers.... After an Analyze Index command, you query the Index_Stats view, and get the ratio of DEL_LF_ROWS to LF_ROWS. If that ratio is over 20% or so (exact mileage varies, depending on how much fluffiness in your indexes you are prepared to tolerate), then it's time to rebuild (bearing in mind that rebuilding an index is not a trivial task -involving table locks, lots of i/o and a hefty double-chunk of disk real estate, you wouldn't want to be *too* aggressive about rebuilding them. 20%-ish is about right).

Regards
HJR
> benny
>
> u518615722_at_spawnkill.ip-mobilphone.net wrote in message
 news:<l.993045842.1372711181@[198.138.198.252]>...
> > After the analyze index, how do you know whether an index needs
> > to be rebuild? I check the DBA handbook, it only say after major
> > data load, you need to rebuild an index. Is there more quatative
> > way to measure an index's usefulness?
> >
> > Thanks
Received on Wed Jun 27 2001 - 04:38:07 CDT