Empty Leaf Blocks After Rollback Part I (Empty Spaces) June 23, 2015

There’s been an interesting recent discussion on the OTN Database forum regarding “Index blank blocks after large update that was rolled back“. Setting aside the odd scenario of updating a column that previously had 20 million distinct values to the same value on a 2 billion row table, the key questions raised are why the blank index leaf blocks and why the performance degradation after the update failed and rolled back.

The key point to make is that an Update is actually a delete/insert operation in the context of indexes. So if we perform a large update, all the previous indexed values are marked as deleted in the index and the new values re-inserted elsewhere within the index structure, potentially filling up a whole bunch of new leaf blocks. If we then decide to rollback the transaction (or the transaction fails and automatically rolls back), then all these newly inserted index entries are deleted potentially leaving behind now empty new leaf blocks in the expanded index structure. Here’s the thing, Oracle will roll back changes to index entries but not changes to the index structure such as block splits.

If an index scan is forced to navigate through these empty leaf blocks, this can indeed potentially have a detrimental impact on subsequent performance.

However, depending on whether the index is Unique or Non-Unique and the type of update being performed, the impact on the index could be quite different.

Now the interesting thing to note here is that for each ID value, we temporarily have the same value twice as we progress and update each ID value (for example, for ID=1, it becomes 2 which already exists. Then the previous ID=2 becomes 3 which already exists, etc.). As the index is Non-Unique, this means when we update say ID=1 to 2, we need mark as deleted the index entry with ID=1 and insert a new index entry with an ID=2. When we update the previous ID=2 to 3, we again mark as deleted the previous indexed value of 2 and insert a new index entry of 3. Etc. Etc.

As we only have 10% of free space available in the index before the update, by updating all rows in this fashion, it means we have to keep performing 50-50 block splits to fit in the new index entries in the corresponding leaf blocks. This effectively results in the index doubling in size as we now have twice the number of index entries (with the previous index entries now marked as deleted).

However, having now performed all these index block splits, if we now roll back the update transaction, it simply means that all the new index entries are deleted and the delete byte removed from the previously deleted entries, with the index structure retaining its newly bloated size. The resultant index block splits are not rolled back. If we look at a new index tree dump:

As all the inserts now occurred in the right-hand most side of the index, Oracle allocated a bunch of new index leaf blocks via 90-10 block splits to store all the new index entries. After the rollback however, all these new entries were removed leaving behind nothing but these new empty leaf blocks which are still part of the overall index structure.

Query performance now depends on what part of the index we need to access.

If we just want to select a single value, then no problem as the ID column is effectively unique and we just need to generally access down to the one leaf block:

4 consistent gets is about as good as it gets for a non-unique Blevel 1 index.

Larger index range scan might need to access additional leaf blocks as they now only contain 1/2 the number of index entries than before, although the additional overhead of such scans would still likely be minimal as most of the work is associated with visiting the table blocks.

One of the worst case scenarios would be having to now plough through all these empty leaf blocks as with the following search for the max ID value:

Oracle uses the Index Full (Min/Max) Scan by starting with the right-most leaf block but as it’s empty, Oracle is forced to make its way across through all the empty leaf blocks until it finally hits upon the first non-empty leaf block that contains the max ID. The excessive 32 consistent gets is due to having to access all these new empty blocks.

A point you may have missed is that the indexes would actually be in a similar state if the transaction had committed rather than rolled back. In the first example, it would have been all the previous index entries that are now marked as deleted and littered throughout the whole index. The index would still be effectively 1/2 filled with useful data. The question remains, will this space be reused by subsequent inserts. Is performance being compromised by having leaf blocks only half filled with useful index entries.

In the second example, we would not have the blank index blocks but we would again have effectively empty blocks in the leaf blocks occupied by the previous index entries. So all the previous leaf blocks would be effectively empty. The max query would indeed return the data straight away but a query based on the min(ID) would now be compromised.

So what needs to be considered is what impact does a large update have on such indexed columns. If possible, if you intend to update all rows of an indexed column, a better strategy would be to mark the index unusable first and then rebuild the index after the update. That way, you significantly improve the performance of the update (as maintaining the index on the fly is really expensive) and you end up with a “perfect” at the end of the process.

So it depends on the size of the update, the type of update and the subsequent impact of SQLs dependent on the index … 🙂

When a oracle does a Coalesce, it does not reallocate the freed blocks. So shouldn’t, the max query still go through the empty leaf blocks? And shouldn’t the index not get it into its original self again as it still has its empty leaf blocks with it after coalesce?

With a coalesce, empty blocks still belong to the index “segment” (and so can’t be used by other segments), but the empty blocks no longer belong within the index “structure” (else what would be the point of the coalesce operation ?). So no, min/max do not therefore have to visit the empty leaf blocks.

The difference between a coalesce and an index shrink is that with a shrink, the empty blocks can be freed from the index segment and re-used by other segments.