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

Jonathan Lewis wrote:
>
> 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.

Hello Johnathan,

I've always thought that if you deleted (even all of the) entries from an index block then it only becomes available for re-use for insert after the delete has committed.

Is this true ?

Could this be a (part) explanation for the index problem the original poster had...ie "lots of deletes, lots of inserts then commit" causing of deleted space not being re-used.

Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse