Deleted Index Entries – Part I (Let It Be)

Just before I hop on a plane to do some training in Europe, thought I might begin a little series on deleted space within an index. I’ll begin with a short piece on how we can determine what deleted space an index may currently have before beginning a discussion on whether this deleted space can indeed be reused by Oracle.

Generally speaking, when an index entry is deleted, Oracle doesn’t physically remove the index entry, it’s simply marked as deleted. It’s another case of Oracle putting off what could be an expensive operation for the current transaction and leaving any potential clean up operations to future processes. However, there’s often some confusion whether these deleted index entries remain “deadwood” within the index structure or whether they are somehow cleaned out later and the space potentially reused by subsequent inserts in the relevant index block.

To set the scene, we begin by creating a very simple scenario. Here we create a little table and associated index, insert a single row, commit it and then delete and commit the row afterwards. We can then have a bit of a look around to see how this deleted index entry is recorded by Oracle.

 SQL> CREATE TABLE test_delete (id NUMBER, name VARCHAR2(10));

Table created.

 

SQL> CREATE INDEX test_delete_idx ON test_delete (name);

 

Index created.

 

SQL> INSERT INTO test_delete VALUES (1, ‘BOWIE’);

 

1 row created.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> DELETE test_delete WHERE id = 1;

 

1 row deleted.

 

SQL> COMMIT;

 

Commit complete.

 

We begin by looking at statistics related to the deleted index entries within the INDEX_STATS view.

 

SQL> ANALYZE INDEX test_delete_idx VALIDATE STRUCTURE;

 

Index analyzed.

 

SQL> SELECT lf_rows, del_lf_rows, del_lf_rows_len FROM index_stats;

 

   LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ----------- ---------------
         1           1              17 

 

So yes, the one and only index entry is a deleted index entry.

 

We can also see how many current deleted entries we have by looking at an index tree dump of the index.

 

SQL> SELECT object_id FROM dba_objects WHERE object_name = ‘TEST_DELETE_IDX’;

 

 OBJECT_ID
----------
     61198

 

SQL> ALTER SESSION SET EVENTS ‘immediate trace name treedump level 61198‘;

 

Session altered.

 

Following is the index tree dump generated by the above operation.

 

—– begin tree dump
leaf: 0x14008d2 20973778 (0: nrow: 1 rrow: 0)
—– end tree dump

 

We notice that the rrow count which is the number of non-deleted index row entries is 0 but the nrow count which is the total index row entries, including deleted entries is 1. Therefore, yes the index currently consists of just the one deleted index row entry.

 

We can also view the deleted index details by performing a dump of the associated index block.

 

SQL> SELECT file_id,block_id FROM dba_extents WHERE segment_name=’TEST_DELETE_IDX’;

 

   FILE_ID   BLOCK_ID
---------- ----------
         5       2257

 

SQL> ALTER SYSTEM DUMP DATAFILE 5 BLOCK 2257;

 

 

System altered.

Below is an  extract from the above index block dump:

     Itl                    Xid                                    Uba                Flag  Lck            Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  —-      0  fsc 0x0000.00000000

0x02  0x0008.024.0000075b  0x00804e29.0078.0b  –U-      1  fsc 0x0011.00000000

  ……

kdxlende 1

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8036

row#0[8021] flag: —D–, lock: 2, len=15

col 0; len 5; (5):  42 4f 57 49 45

col 1; len 6; (6):  01 40 10 0a 00 00

 

From the above, kdxlende 1 is a count of the deleted index entries. The index entry has a D flag set, signifying that the index entry has been deleted. Also note that the index entry was locked and deleted by the ITL entry associated with ITL number 2.

 

So yes, when we perform a delete that results in the deletion of an index row entry, the deleted index entry is marked as deleted but is not physically cleaned out at the time of the delete. All the above checks confirm this current state of the index.

 

The key question is therefore, are these deleted index entries ever reused/removed, or are they forever “deadwood” that would require a periodic rebuild of the indexes to clean out ?

 

Answer coming soon …