So far, everything is as expected. With have an index with 200,000 rows that currently has 374 leaf blocks.

OK, what we want to do is basically gradually delete the current set of rows and replace them with 200,000 new rows, with ever-increasing Primary Key values. To this end, we create the following procedure:

So the procedure basically determines the current MIN and MAX values of our PK column and gradually deletes the current rows and then inserts new ones. Every 1000 iterations, we commit the changes. Nothing too complex here.

Things have gone even worse. We still only have 200,000 rows in the table but the index now has an additional 147,841 deleted entries and the number of leaf blocks has again increased substantially to 994 leaf blocks.

We notice that the first portion of the index contains leaf blocks with nothing but deleted index entries. The number of rrows is 0 for a vast number of leaf blocks. We also notice that the root block has a rba of 0x100378b 16791435, which is only a few values below some of the rba values of the left most indexes in the index structure (say) 0x100378e 16791438. Therefore, this highlights that even though these left most blocks in the index structure contain nothing but deleted index entries, Oracle is not recycling them as it should do. Oracle is simply adding new blocks to the index structure rather than recycling empty leaf blocks, resulting in the index growing bigger and bigger.

The leaf blocks however at the right most end of the index structure (the second portion of the partial treedump), shows us a nice compact set of leaf blocks with lots of index entries per block (most with 533 per leaf block) and with no deleted index entries (rrows matches the nrows value).

If we run the procedure 10 times in total, we get an index that looks like the following:

We now have 1,125,132 deleted index entries and the index is now over 10 times the original size, up from 374 to a massive 4,136 leaf blocks, even though the table only contains 200,000 rows.

There are a number of contributing factors here :)

The question is why, why is the index behaving in this fashion and what can we do to ensure the index doesn’t grow in this manner and can remain basically the same size as we delete and insert new rows into the table ?

Hi,
as far I know when using unique index Oracle will reuse marked as deleted index slots even in the same transaction.
So maybe the problem is we are commiting every 1000 rows .
Just a wild guess .
Regards
GregG

They will only use the same slots in a unique index in the same transaction if the values are the same, which isn’t the case here. So in a unique index, the delete of an ID 42 followed by an insert of 42 would simply reuse the slot for 42, whereas in a Non-Unique index, an additional 42 index entry is created and the deleted entry remains.

i wonder if you could delete/commit/insert (without harming business transaction integrity)? Current method of delete/insert means, oracle needs to account for a possible rollback of whole transaction.

This would certainly reduce some of the slippage in growth due to the deletes not being cleaned out within the same transaction. But it would be less efficient and indeed could stuff up or put at risk business integrity.

Notice that the leaf blocks only increase by 11 after 10 iterations of the procedure. The problem is caused by Oracle not releasing deleted leaf blocks to the pool until the branch they belong to is completely empty. The reverse key index makes more of the new keys fit into the existing ranges so the branch doesn’t need to be completely empty and the leaf blocks can be reused. That being said it’s not a common occurrence to use a reverse key index for a primary key unless block contention rears its ugly head, however it is nice to know that taking the time to create a reversed primary key can mitigate the problem.

Although Reverse Key index won’t change the problem as such, it does make it Irrelevant as we now don’t have empty leaf blocks to worry about. All the inserts will basically go into leaf blocks that contain some data and will simply clean out any deleted deadwood that might exist.

I agree you should, especially with code dependant on correct stats. Dynamic sampling will likely be OK in this scenario though, although the recursive SQL could add to the problem of the leaf blocks being locked away due to the bitmap space management.

I believe I said what you did in my response, using more words. :D And you’re welcome for the demo — I tend to play with Oracle as much as I can and set up examples to examine and possibly explain observed behaviour. Thank you for providing yet another avenue to pursue with my curiosity.