Deleted Index Entries Part II (V-2 Schneider)

I’m back !!

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.

SQL> CREATE TABLE del_stuff (id NUMBER, name VARCHAR2(30));

Table created.

SQL> CREATE INDEX del_stuff_i ON del_stuff(id);

Index created.

SQL> INSERT INTO del_stuff SELECT rownum, ‘Bowie’ FROM dual CONNECT BY level <=10;

10 rows created.

SQL> COMMIT;

Commit complete.

Next, deleted say 4 of the rows from the table.

SQL> DELETE del_stuff WHERE id in (2,4,6,8);

4 rows deleted.

SQL> COMMIT;

Commit complete.

A view of INDEX_STATS after a ANALYZE … VALIDATE STRUCTURE command will show the following:

LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE
------- ----------- --------------- -------
     10           4              56     140

 

Note: It clearly shows 4 index entries are indeed currently marked as deleted.

 

A treedump will show the following:

 

—– begin tree dump

leaf: 0x1402e3a 20983354 (0: nrow: 10 rrow: 6)

—– end tree dump

 

Note: It only shows 6 rrow but 10 nrow values, clearly suggesting there are currently 4 deleted index entries.

 

A partial index block dump will show the following:

 

kdxlende 4

 

row#1[7928] flag: —D–,lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 40 2e 32 00 01

 

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:

 

SQL> INSERT INTO del_stuff VALUES (100, ‘New Row’);

 

1 row created.

 

SQL> COMMIT;

 

Commit complete.

 

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

 

That indeed, INDEX_STATS no longer has any statistics of deleted index entries.

 

—– begin tree dump
leaf: 0x1402e3a 20983354 (0: nrow: 7 rrow:  7)
—– end tree dump

 

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 …