Re: B-Tree index

"Brian Peasland" <oracle_dba_at_peasland.com> wrote in message news:3DC938AC.2058C850_at_peasland.com...
> Your B-tree index will always be balanced. That's part of its
> definition! What you really want to check is to see if the leaf nodes
> are "empty". You can do this by issuing the following command:
>
> ANALYZE INDEX index_name VALIDATE STRUCTURE;
>
> Then query V$INDEX_STATS comparing the deleted leaf nodes to the total
> number. If this percentage is too high, then rebuild the index.
>

First of all, if a leaf node is completely empty, then it can be reused by any new index entry, regardless of its value, and there's absolutely no need to rebuild to make it happen.

Secondly, the INDEX_STATS view isn't a V$, just "INDEX_STATS".

Thirdly, the columns you are thinking of are 'LF_ROWS' and 'DEL_LF_ROWS'. It's the ratio of deleted *rows* to non-deleted row that's important, and nothing to do with *nodes*.

Fourthly, even if the number of deleted leaf rows is high, that is not an automatic case for rebuilding the index: deleted leaf rows get re-used the next time a new entry gets made that can make use of the space they occupy. Delete "BILL" and insert "BRIAN", and you will find zero deleted leaf rows in the NAME index. Of course, if your index is on a monotonically incrementing sequence number, then you will never insert a new value that can slot into the space vacated by a previous value, and therefore the number of deleted leaf rows will continue to grow... and that might then be a case for rebuilding.

But generally, indexes rarely need to be rebuilt, precisely because Oracle's re-balancing and leaf-clearing mechanisms are so efficient.

Regards
HJR
> HTH,
> Brian
>
> Harry Sheng wrote:
> >
> > How can I check if a B-Tree index (suppose it is not too big) is well
> > balanced ?
Received on Fri Nov 08 2002 - 14:18:46 CST