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

I've been thinking about that one.

The original poster said that the used size of the index grew by a factor of three on inserts only, and made no mention of updates or deletes which would make it easy to generate such a discrepancy.

The point at which an index block splits is dependent upon the notional location of the new row in the block and the location of the block in the leaf level, so it may be possible to insert sequences of value where:

    the block split 1/3, 2/3
    the entry and all further entries go into the 2/3 full block     the block fills and splits 1/3, 2/3

Over time every block would end up 1/3 full.

The drawback to the hypothesis is that it seems to require an obviously aberrant data distribution that you would suspect that the index was going to be a problem index anyway.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Keith Boulton wrote in message
<38ca1e3e.757609_at_read.news.globalnet.co.uk>...


>On Fri, 10 Mar 2000 23:39:03 -0000, "Jonathan Lewis"

><jonathan_at_jlcomp.demon.co.uk> wrote:

>

>>

>>Branch blocks can be re-used when empty

>>(at least in recent versions of Oracle).

>>

>

>I'm more than happy to stand corrected. I  thought I saw a post about

>this a while ago saying that because of impact on concurrency, branch

>blocks may not be cleaned up - I must have been imagining it.