Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: rebuild this index
Hi Norman
Thanks for the reply. Please see my comments/questions below.
Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA703BDA2F6_at_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) ?
No. It is not.
> If so, then these will need to be rebuilt because deleted entries will
> not be reused.
>
This I understand. In fact, yesterday I rebuilt one index where deleted entries occupied much of the space in the index and the index values are ever increasing.
> 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 !
Can I assumed that if deleted rows in the index is 0, most of the unused space in the index is due to block splits? This (block split) is something that I dont quite fully understand yet. Do you know of any good document for this?
>
> 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
> -------------------------------------
>
<SNIP> Received on Tue Jun 03 2003 - 21:59:45 CDT