rebuild this index

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Tue, 3 Jun 2003 10:16:54 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703BDA2F6@lnewton.leeds.lfs.co.uk>

Morning Daud,

as ever, it depends !

What are the columns of the index ?
Are they based on an ever increasing/decreasing sequence or date
(leading columns) ?

If so, then these will need to be rebuilt because deleted entries will not be reused.

If the indexed columns are based on fairly random data - say names etc - then there is every chance that a deleted entry will be reused at some point, so rebuilding is not necessarily a requirement.

As for the percentage used space, what if you have just split a block ? The pct_used goes down because two blocks now have empty space in them wheras before the split, one was full. If the split caused the 'parent' block to split, then you have 4 blocks with space and so on up the height of the index. So, the fact that the space used went down may not be a problem. It depends !

As for the stats you have posted, there are no deleted rows in the index
(DEL_LF_ROWS = 0) so I wouldn't go rebuilding this one myself. I suspect
the free space you have in this index (41%) is probably due to the way the index was created and maybe a recent block split.

Cheers,
Norm.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: daud11_at_hotmail.com (Daud) [mailto:daud11_at_hotmail.com] Posted At: Tuesday, June 03, 2003 8:21 AM Posted To: server
Conversation: rebuild this index
Subject: rebuild this index

Is this index a good candidate for rebuild? Oracle documentation says that one should look at the ratio of del_lf_rows / lf_rows where if it is more than, say 0.2, then it is a good candidate for rebuild. Now, for the index below the ratio is 0. The height looks good. But I have read somewhere that says if PCT_USED is low (< 70%??) then it is time to rebuild the index. Any comments for the index below?

<SNIP details> Received on Tue Jun 03 2003 - 04:16:54 CDT