15 October, 2009

Indexes Growing Larger After Rebuilds

Here's a note by Richard Foote about Indexes that can grow *larger* when rebuilt.


LIU GaoYuan said...

Not in Oracle, but in DB2, I need to move one table with index of 20GB, so I did some rebuild tests on the table with different methods:
1) DB2 reorg command,
2) DB2 Load with cursor command to a new table
3) DB2 "insert ... select" to a new table
And it turns out that method 1 & 2 result in the index with about 20GB still, and method 3 has the index to be about 30GB!

Pascal said...

Hello Hemant;

Suppose I deleted value "2" from table.which is indexed.
Is this index space occupied by value 2 ever used again? or do I need to insert same value again
to use this space?

In other example:
Supose I have table with values 1 to 500.000.
When I delete all even numbers,Are these space ever used again?

Does coalece automatically gives space to freelist?

Hemant K Chitale said...

An Index is an *organized* structure. A leaf block that has existing values can allow inserts of only values that fit within the minimum or maximum (or it might have to suffer a block split).

Thus if "2" existed between "1" and "3" in only 1 leaf block and is deleted, that entry is reused only by another entry "2" (we are assuming integer numbers here).
If there are multiple "2"'s they are sorted by rowid.
Extend the logic to multiple even numbers.

A coalesce can release blocks to the free list.

Hemant K Chitale

Pascal said...

That means that, the deleted space will not be reused unless I insert same value....

Rahul said...


Nice and interesting article. Thanks a lot.

Thanks and regards,
Rahul Jain

arjudba said...

intresting article, i am very much intrested in this article, keep on posting

Anonymous said...

Dear Hemant,
I read from Richard foote Blog,he said that even in the leaf block, a new insert will reuse deleted/free space even though it is different.
His(Richard foote) line from blogpost

"That’s it, that’s all it takes. Just one new index entry in a leaf block will automatically clean out all associated deleted index entries that may currently exist within the block. Even if there are hundreds of deleted entries with the leaf block, just the one new index entry will clean them all out. Note the new index value doesn’t have to be the same as any of the deleted index entries, it doesn’t even have to be within the range of any of the deleted index entries within the block . Any new index entry within the leaf block will do the job quite nicely."

Above (in bold characters) he hasn't anything said about leaf block. He has mentioned about a BLOCK only.But Leaf-Blocks are also BLOCKs.

1.So if it is 4 instead of 2, it(4) should fit into the deleted spot of 2(?)
2.So does that mean That perticular leaf block space is Unused for ever until it doesn't get single digit value ?

-Nitin Joshi
PS Sorry if i've commented same thing multiple times(Network Problem)

Hemant K Chitale said...


That new entry would clean out the block if it *belongs* to the leaf block. If you see Richard's example, the new entry, while not being in the range of 1 to 10 (min and max values of the leaf block), still does go into the leaf block. Why ? Because there is no other leaf block that has values 11 or higher ! This leaf block has free space and 100 doesn't need to go to any other leaf block -- it can be accomodated into the same block, just bumping up the max value from 10 to 100.
I wouldn't say that this would happen in a real world where you have an index that has multiple leaf blocks !

Anonymous said...

Yes.got it.
Took a time to get to know.
Thank You.