Re: Index size growing abnormally .....Oracle bug ?

On Fri, 10 Mar 2000 14:39:39 +0100, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>This is NOT a bug, this is feature of Oracle using the B+-tree concept (many
>other vendors do that)

I'm not convinced.

>A B+-tree is always balanced. Whenever a new level needs to be added in one
>branch, that level is added in all other branches also.

Not true. The b*tree index works by splitting blocks as they become full. Normally, if a leaf block has no more space, the leaf block is split in two with half the entries going to a new leaf block and a new entry added to the parent index node block. If that causes the node block to overflow, the node block is likewise split and so on up the chain of node blocks. This behaviour automatically balances the index on inserts. Only the leaf block and (in less than 5% of cases, its parent node block ) are affected. This behaviour also means that an index block is normally 75% full.

>> I insert a lot of rows in my table (about 1 million).
>> When i recompute the ratio row size/index size is about 3.
>> I rebuild my index and the ratio has been decreased to 1.
>> (1 Go has been freed with the reorganisation!)

I suspect the problem here is to do with the fact there is special logic in the Oracle index maintenance code for sequentially increasing keys such that in the leaf block split, only the new entry goes to the new block. This will result in near 100% space utilisation when inserting rows. During an index rebuild however, the index space usage parameters will be used which will result in less efficient space utilisation and therefore a larger index.

As I understand it, there are problems with deletions such that if a leaf block is cleared of all entries, it becomes available for re-use, but an index node block is never re-used even when it becomes empty. The implication of this is that index space usage may grow over-time because it contains a lot of unused blocks, but this will not be a performance issue because the index is always balanced. Received on Fri Mar 10 2000 - 00:00:00 CST