Deleted Index Entries Part II (V-2 Schneider) June 18, 2008

In Part I, we looked at how index entries are only marked as deleted during delete/update operations. This means the DML operation and resultant transaction doesn’t have to concern itself with physically cleaning out the deleted entries.

The question is therefore, are these deleted index entries “deadwood”, wasted space that makes the index less compact and less efficient over time requiring a potential periodic index rebuild, or can this deleted space be subsequently reused by Oracle ?

There’s a common perception that deleted space within an index is indeed “deadwood” or can only be reused in very specific circumstances, such as when an identical index value is subsequently re-inserted.

However, this is just another of Oracle’s infamous urban myths. In the vast majority of cases, deleted space within an index can indeed be recycled and can indeed be subsequently reused. Today, I’m just going to begin by focusing on the most common method by which deleted index entries are generally cleaned up.

All it takes to clean out all the deleted index entries within a specific index leaf block is a subsequent insert in the block. 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.

A very simple demonstration I use to highlight this point. First, create a simple table and associated index with 10 rows.

That indeed there are currently 4 index entries marked as deleted with the ‘D’ flag within the index block.

However, just a single subsequent insert will clean out all 4 of these deleted index entries. Note the new value (100) is not the same value as any of the previously deleted entries and is not even within the range of previously deleted index entries:

That the Index Tree dump no longer has any record of the deleted index entries.

That the index block dump no longer has any record of the deleted index entries and that:

kdxlende 0

the count of deleted index entries within the block has been reset to 0.

For most randomly inserted indexes, this means deleted entries will be eventually automatically cleaned out and the freed space reused by subsequent insert operations. Worrying about deleted space and rebuilding such indexes is typically unnecessarily.

I’ll next discuss another method by which Oracle will clean out and remove deleted index entries from its indexes …

Share this:

Like this:

Related

Do you obtain this
— ———————
Now, if we look at the same leaf block, we find:
LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE
——- ———– ————— ——-
7 0 0 98
— —————————————
Without doing again and analyze validate structure of the index???
and if so, do this means that the deleted space is really cleaned up after an analyze validate structure or just after the insert??

As Oracle is already going to the trouble of reading the leaf block into memory and adding a new index entry, clearing out the deleted stuff is only going to add a relatively minor amount of additional overhead.

This index would certainly be worth some further investigation. Assuming you use all the digits for the numeric columns, that’s approximately 12 byes + 6 bytes for the rowid, + 2 bytes for the column length bytes + 2 bytes additional overhead for a max of say 22 bytes per index entry. Smaller numbers may only need around 2 bytes each so 22 bytes is pushing it.

Now looking at your numbers, if you divide the bytes in the index by the rows in the table, that’s roughly 72 bytes per index entry. Even allowing for block overheads, branch blocks, free space, etc. it does appear your index has lots of potential free space.

Even allowing for 50-50 block splits, it’s still very excessive so something doesn’t appear right here. Are you sure there have been no deletes ? How are rows inserted into the table ? When was the index created and what was the pctfree value ?

Certainly an index with unusual characteristics and worthy of some investigation.

My suspicion based on what info I have is that the index is generally performing 50-50 block splits but the free space is not being utilised as would normally be expected.

The table, despite it’s definition appears to have quite a low average row size (the bytes divided by number of rows is around 49 bytes per row, then less block overheads, pctfree etc.) so I would guess many of the fields have null or short values.

So the average index entry length may therefore not too far from say half that of the average table row length.

Therefore, with only 50% of an index block being used, the numbers start to merge somewhat if the 50% of generate free space is not ever used.

Is it possible that the table has some kind of outlier value ? Do most of the PKs monotonically increase but there’s a maximum value in there somewhere already existing that the new monotonically increasing PK entries never hit ?

This could possibly result in 50-50 splits which generates free space that never gets used and makes the index therefore grow at a higher rate than the table.

What is the maximum key value and what are the typical new PK values that get generated ?

I tried the same example but with a partitioned table and a locally partitioned index. It seems for me that I didn’t came to the same result. Do the cleaned index space is also valid for partitioned indexes (oracle 8.1.7.4).

In a scenario of IOT, when a leaf block is 50% full and one row in the middle of the sorted index key entries in that block is deleted. And then row with same index key values but more row size is inserted again then in that case how will it be stored? Since the row cann’t fit in the same place because of more row size.