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

This is NOT a bug, this is feature of Oracle using the B+-tree concept (many other vendors do that)
for indexing.
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. What is most likely, you are inserting at the 'logical end' or 'logical beginning' of the index.
Your data is highly skewed. This will result in extra B+-tree levels and frequent rebuilds.
You are describing the typical symptoms here. The only resolution is to create the index with a higher pctfree. The default is either 5 or 10 percent, increase to 20, and see what happens. This will make the index initially bigger, but that's the only negative impact.

Hth,

Sybrand Bakker, Oracle DBA

Eric DUCHET <eric.duchet_at_free.fr> wrote in message news:KO6y4.496$a24.3549288_at_nnrp3.proxad.net...
> Hi,
>
> I have a database with lots of inserts and a lot of index.
> I have at the start 1 million rows on a table, and indexes on that table.
> The ratio total rows size/total index size is nearly 1.
>
> 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!)
>
> My conclusion is that the index size grows abnormally.
> I think this is an Oracle bug.
> And a reorganisation must be done when the ratio data/index is growing
> to resolve this bug.
>
> Is there anonybody have this problem ?
> Is there any solution to resolve that ?
>
> Thanx
>
>
>
Received on Fri Mar 10 2000 - 00:00:00 CST