Deleted Index Entries – Part I (Let It Be) June 8, 2008Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes, Oracle Myths.
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));
SQL> CREATE INDEX test_delete_idx ON test_delete (name);
SQL> INSERT INTO test_delete VALUES (1, ‘BOWIE’);
1 row created.
SQL> DELETE test_delete WHERE id = 1;
1 row deleted.
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;
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‘;
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;
Below is an extract from the above index block dump:
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0008.024.0000075b 0x00804e29.0078.0b –U- 1 fsc 0×0011.00000000
row#0 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 …