Deleted Index Entries – Part I (Let It Be) June 8, 2008
Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes, Oracle Myths.trackback
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 …
Hi Richard,
I wonder how can one person write so much about indexes. You really posses exceptional technical skills.
Coming back to your question:
“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 ?”
I continued you test case and yes Oracle removes all the rows marked as “Deleted” and reuses this space when a new row is added to that particular block:
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
SQL> INSERT INTO test_delete VALUES (1, ‘BOWIE’);
1 row created.
SQL> COMMIT;
Commit complete.
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 0 0
Continuing further, I inserted 10,000 records and then deleted all of them. Later, when I inserted one row, I found Oracle has removed all the “Deleted” entries for that particular block leaving other blocks as is.
SQL> alter table test_delete modify(name varchar2(100));
Table altered.
SQL> truncate table test_delete;
Table truncated.
SQL> insert into test_delete select rownum, ‘Bowie-‘||rownum from dual connect by level commit;
Commit complete.
SQL> delete from test_delete;
10000 rows deleted.
SQL> commit;
Commit complete.
SQL>
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
———- ———– —————
10000 10000 218894
SQL> insert into test_delete values (1, ‘Bowie’);
1 row created.
SQL> commit;
Commit complete.
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
———- ———– —————
9812 9811 214759
SQL>
Regards
LikeLike
As far as I know, the deleted space will be reused :
SQL> create index emp_eno_idx on emp(eno);
Index created.
SQL> analyze index emp_eno_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
———- ———– —————
4 0 0
SQL> delete from emp where eno =1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> analyze index emp_eno_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
———- ———– —————
4 1 14
SQL> insert into emp values (1,’JAFFAR’,SYSDATE);
1 row created.
SQL> commit;
Commit complete.
SQL> analyze index emp_eno_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
———- ———– —————
4 0 0
Jaffar
LikeLike
This is great stuff. Awaiting for the next post…
Thanks,
Karthick.
http://www.karthickarp.blogspot.com/
LikeLike
Hello Richard,
I’m only on my second cup of tea so I am not sure I am reading this correctly:
[begin quote]
—– begin tree dump
leaf: 0×14008d2 20973778 (0: nrow: 1 rrow: 0)
—– end tree dump
We notice that the nrow count which is the number of non-deleted index row entries is 0 and the rrow count which is the total index row entries, including deleted entries is 1.
[end quote]
It looks like the nrow and rrow values are reversed in your narrative. That is, nrow is 1 (not 0) and rrow is 0 (not 1) in the dump output. Also, isn’t nrow the number of all entries (including deleted entries) rather than “the number of non-deleted index row entries” and rrow the the current number of entries rather than “the total index row entries”? Sorry, just a bit confused by the narrative!
Cheers,
Mark
LikeLike
Hi Mark
Thank you, yes I mentioned the nrow and rrow the wrong way around.
It’s now fixed.
You need a couple of cups of tea, I obviously need a stronger cup of coffee !!
Thanks again 🙂
LikeLike
As usual, very clear and interesting explanations on Oracle internals…
As a side note, there are two small typos near the end : “Also note that the index entry *is* was locked and deleted by the ITL entry associated with ITL number 2.” (“is” should not be there) and “So yes, when we perform a delete that result in the *deleted* of an index row entry” (should be “deletion”).
LikeLike
Hi Al
Fixed.
Mucho thnaks for spoting oll off mi typo erors 😉
LikeLike
>Mucho thnaks for spoting oll off mi typo eror
That would be typo-errors. A typo error is spelling it correctly. Similar to near-miss and near miss.
I know, i know, that was just a typo. 🙂
LikeLike
Hi Brian
That’s it, bed time. I can’t cope anymore !!
Cheers 😉
LikeLike