Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to know when an index needs rebuild ?
Mark wrote:
> Hi all dba users,
> I'd like to know if there's some good sql script / indicator to detect if
> an index need to be rebuilt.
> Thanks a lot
> Mark
Indexes seldom need to be rebuilt. Simple as that. And you won't go far wrong if you just forget about rebuilding indexes altogether. Oracle's indexing mechanism is pretty damn efficient without periodic rebuilds. And rebuilds are expensive options, causing a lot of I/O, and a lot of exclusive table locking. The balance of costs and benefits is definitely on the 'leave them alone' side.
However, it can get a bit subtler than that.
If you have an index which is built upon a sequence number, and you do occasional deletes from the table, then that probably needs periodic rebuilding, because you'll likely never re-use the index space freed by the deletes. If you do bulk deletes on that same table, however, then entire index blocks will be cleared by the deletes, and that space can be re-used, so even there you won't need to rebuild the index.
But if you want the 'official' line, which is not particularly a wise move, then analyze index blah validate structure, and then query the index_stats view. If DEL_LF_ROWS is more than 15% (9i performance tuning course notes) or 25% (elsewhere in the same performance tuning course notes!) then a rebuild *might* be in order.
But even Oracle can't stick to one percentage figure, and that's because there *is* no magic answer.
Regards
HJR
Received on Thu Sep 25 2003 - 06:43:20 CDT
![]() |
![]() |