jump to navigation

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 …

About these ads

Comments»

1. Asif Momen - June 8, 2008

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

2. Syed Jaffar Hussain - June 8, 2008

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

3. karthickarp - June 10, 2008

This is great stuff. Awaiting for the next post…

Thanks,

Karthick.

http://www.karthickarp.blogspot.com/

4. Mark A. Williams - June 15, 2008

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

5. Richard Foote - June 17, 2008

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 :)

6. Al - June 25, 2008

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”).

7. Richard Foote - June 25, 2008

Hi Al

Fixed.

Mucho thnaks for spoting oll off mi typo erors ;)

8. Brian Tkatch - June 26, 2008

>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. :)

9. Richard Foote - June 26, 2008

Hi Brian

That’s it, bed time. I can’t cope anymore !!

Cheers ;)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,852 other followers

%d bloggers like this: