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

I also encountered this problem: a very small table (15 rows) that was a running total persistent store, that got updated about 200,000 times per day. One of its indexes would grow from 16K to over 6MB within a week, overflowing the allowed number of maximum extents. Turns out that it was because I was updating a column (total minutes) that I included in one of my concatenated indexes. According to Oracle, this is exactly how Oracle will behave based on its architecture and how it writes new leaf blocks, etc.

I realized the reason I was including this column in the first place was part of a tuning effort, and for this table I wanted to eliminate access to the table. This is essentially what an index-only table gives you. However, rather than converting it to an IOT, I found that removing this column from my index and allowing the table access (especially with only 15 rows that are certainly cached) had no impact, and solved my cancerous index problem.

Ours is a VLDB database with tables in the 10GB range so yes I'm familiar with larger tables like the one you describe, in case you had misgivings about my information given the small table in the example.

best of luck,
bill c.

"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