Deleted Index Entries

最新推荐文章于 2024-06-30 16:55:40 发布

loryliu

最新推荐文章于 2024-06-30 16:55:40 发布

阅读量1.5k

点赞数

分类专栏： oracle 文章标签： index block reuse

oracle 专栏收录该内容

203 篇文章

订阅专栏

本文探讨了Oracle数据库中索引删除操作后删除项的处理方式。通过一系列实验验证了删除项并非永久占用空间，而是可以被后续插入操作清理及复用。文章详细介绍了不同情况下Oracle如何自动清理这些删除项，保持索引效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Deleted Index Entries – Part I (LetIt Be) June 8, 2008

Postedby Richard Foote in IndexDelete Operations, OracleGeneral, OracleIndexes, OracleMyths.
trackback

Just before Ihop on a plane to do some training in Europe, thought I might begin a littleseries on deleted space within an index. I’ll begin with a shortpiece on how we can determine what deleted space an index may currentlyhave before beginning a discussion on whether this deletedspace can indeed be reused by Oracle.

Generallyspeaking, when an index entry is deleted, Oracle doesn’t physically remove theindex entry, it’s simply marked as deleted. It’s another case of Oracle puttingoff what could be an expensive operation for the currenttransaction and leaving any potential clean up operations to futureprocesses. However, there’s often some confusion whether these deletedindex entries remain “deadwood” within the index structure or whetherthey are somehow cleaned out later and the space potentiallyreused by subsequent inserts in the relevant index block.

To set thescene, we begin by creating a very simple scenario. Here we createa little table and associated index, insert a single row, commit it andthen delete and commit the row afterwards. We can then have a bit of alook 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> CREATEINDEX test_delete_idx ON test_delete (name);

Index created.

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DELETEtest_delete WHERE id = 1;

1 row deleted.

SQL> COMMIT;

Commit complete.

We begin bylooking at statistics related to the deleted index entries within theINDEX_STATS view.

SQL> ANALYZE INDEXtest_delete_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECTlf_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 oneand only index entry is a deleted index entry.

We can also seehow many current deleted entries we have by looking at an index tree dumpof the index.

SQL> SELECTobject_id FROM dba_objects WHERE object_name = ‘TEST_DELETE_IDX';

OBJECT_ID

----------

61198

SQL> ALTERSESSION SET EVENTS ‘immediate trace name treedump level61198‘;

Session altered.

Following is theindex tree dump generated by the above operation.

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

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

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

SQL> SELECTfile_id,block_id FROM dba_extents WHERE segment_name=’TEST_DELETE_IDX';

FILE_ID BLOCK_ID

---------- ----------

5 2257

SQL> ALTERSYSTEM DUMP DATAFILE 5 BLOCK 2257;

System altered.

Below isan 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 fsc0x0011.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 theabove, kdxlende 1 is a count of the deleted index entries. Theindex entry has a D flag set, signifying that the index entryhas been deleted. Also note that the index entry was locked and deleted bythe ITL entry associated with ITL number 2.

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

The key questionis therefore, are these deleted index entries ever reused/removed, or are theyforever “deadwood” that would require a periodic rebuild of the indexes toclean out ?

Answer comingsoon …

About these ads

Share this:

· Facebook

· Twitter

Related

Deleted IndexEntries Part IV (Breaking Glass)In "IndexDelete Operations"

Updates andIndexes Part II (Down Is The New Up)In "IndexDelete Operations"

Deleted Index EntriesPart II (V-2 Schneider)In "Index Delete Operations"

Comments»

1. Asif Momen - June 8, 2008

Hi Richard,

I wonder how canone person write so much about indexes. You really posses exceptional technicalskills.

Coming back toyour question:

“The keyquestion is therefore, are these deleted index entries ever reused/removed, orare they forever ”deadwood” that would require a periodic rebuild of theindexes to clean out ?”

I continued youtest case and yes Oracle removes all the rows marked as “Deleted” and reusesthis space when a new row is added to that particular block:

SQL> SELECTlf_rows, del_lf_rows, del_lf_rows_len FROM index_stats;

LF_ROWSDEL_LF_ROWS DEL_LF_ROWS_LEN
———- ———– —————
1 1 17

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ANALYZEINDEX test_delete_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECTlf_rows, del_lf_rows, del_lf_rows_len FROM index_stats;

LF_ROWSDEL_LF_ROWS DEL_LF_ROWS_LEN
———- ———– —————
1 0 0

Continuingfurther, I inserted 10,000 records and then deleted all of them. Later, when Iinserted one row, I found Oracle has removed all the “Deleted” entries for thatparticular block leaving other blocks as is.

SQL> altertable test_delete modify(name varchar2(100));

Table altered.

SQL> truncatetable test_delete;

Table truncated.

SQL> insertinto test_delete select rownum, ‘Bowie-‘||rownum from dual connect by levelcommit;

Commit complete.

SQL> deletefrom test_delete;

10000 rowsdeleted.

SQL> commit;

Commit complete.

SQL>
SQL> ANALYZE INDEX test_delete_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECTlf_rows, del_lf_rows, del_lf_rows_len FROM index_stats;

LF_ROWSDEL_LF_ROWS DEL_LF_ROWS_LEN
———- ———– —————
10000 10000 218894

SQL> insertinto test_delete values (1, ‘Bowie’);

1 row created.

SQL> commit;

Commit complete.

SQL> ANALYZEINDEX test_delete_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECTlf_rows, del_lf_rows, del_lf_rows_len FROM index_stats;

LF_ROWS DEL_LF_ROWSDEL_LF_ROWS_LEN
———- ———– —————
9812 9811 214759

SQL>

Regards

2. Syed Jaffar Hussain - June 8, 2008

As far as Iknow, the deleted space will be reused :

SQL> createindex emp_eno_idx on emp(eno);

Index created.

SQL> analyzeindex emp_eno_idx validate structure;

Index analyzed.

SQL> SELECTlf_rows,del_lf_rows,del_lf_rows_len from index_stats;

LF_ROWSDEL_LF_ROWS DEL_LF_ROWS_LEN
———- ———– —————
4 0 0

SQL> deletefrom emp where eno =1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> analyzeindex emp_eno_idx validate structure;

Index analyzed.

SQL> SELECTlf_rows,del_lf_rows,del_lf_rows_len from index_stats;

LF_ROWSDEL_LF_ROWS DEL_LF_ROWS_LEN
———- ———– —————
4 1 14

SQL> insertinto emp values (1,’JAFFAR’,SYSDATE);

1 row created.

SQL> commit;

Commit complete.

SQL> analyzeindex emp_eno_idx validate structure;

Index analyzed.

SQL> SELECTlf_rows,del_lf_rows,del_lf_rows_len from index_stats;

LF_ROWSDEL_LF_ROWS DEL_LF_ROWS_LEN
———- ———– —————
4 0 0

Jaffar

3. karthickarp - June 10, 2008

This is greatstuff. 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 mysecond 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 thatthe nrow count which is the number of non-deleted index row entries is 0 andthe rrow count which is the total index row entries, including deleted entriesis 1.
[end quote]

It looks likethe 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 numberof all entries (including deleted entries) rather than “the number ofnon-deleted index row entries” and rrow the the current number of entriesrather than “the total index row entries”? Sorry, just a bit confused by thenarrative!

Cheers,

Mark

5. Richard Foote - June 17, 2008

Hi Mark

Thank you, yes Imentioned the nrow and rrow the wrong way around.

It’s now fixed.

You need acouple of cups of tea, I obviously need a stronger cup of coffee !!

Thanksagain :)

6. Al - June 25, 2008

As usual, veryclear 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 forspoting oll off mi typo erors ;)

8. Brian Tkatch - June 26, 2008

>Mucho thnaksfor spoting oll off mi typo eror

That would betypo-errors. A typo error is spelling it correctly. Similar to near-miss andnear miss.

I know, i know,that was just a typo. :)

9. Richard Foote - June 26, 2008

Hi Brian

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

Cheers ;)

Deleted Index Entries Part III (Slip Away) June 23, 2008

Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths.
trackback

Another little post while I look after some unwell munchkins …

I’ve already looked at the most common example of when Oracle will automatically clean out deleted index entries, that being any subsequent insert into a leaf block will clean out the deleted entries that may exist from the associated leaf block.

Another example of Oracle automatically removing deleted index entries is that associated with a variation of delayed block cleanout. If an Oracle index block with deleted index entries is written to disk before the associated transaction performing the index delete operation is committed, the next time the index block is accessed, Oracle will not only clean out the transaction details from the index block (such as the lock byte) but the deleted index entries themselves may also be cleaned out as well.

This scenario is most likely to occur during large or long running transaction operations (such as batch operations) where many rows are likely to be accessed and/or modified and the associated modified index blocks may get aged out of the buffer cache and written to disk before the transaction ends via the COMMIT.

Note this delayed clean out does not require the index block to be accessed via a subsequent DML operation, even a simple SELECT statement will be sufficient to perform the necessary clean out of deleted index entries.

To illustrate this behaviour, basically create a table with a bunch of rows, deleted some of them but flush the buffer cache prior to issuing the commit on the delete.

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 <=1000;

1000 rows created.

SQL> COMMIT;

Commit complete.

Next, deleted say 1/2 of the rows from the table.

SQL> DELETE del_stuff WHERE mod(id,2) = 0;

500 rows deleted.

At this point, we flush the associated blocks to disk to simulate a large or long running transaction is which blocks may be aged from the buffer cache and written to disk before the COMMIT is performed.

SQL> ALTER SESSION SET EVENTS ‘immediate trace name flush_cache';

Session altered.

in 9i, or since 10g:

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

It’s only at this point after the blocks are flushed to disk that the COMMIT is performed.

SQL> COMMIT;

Commit complete.

Once the index blocks are subsequently accessed, we notice the deleted index entries may have already been cleaned out …

NOTE: The following results do not consistently occur if the index consists of just a single block (the root block is a “special” case), but does appear to be more consistent if the index has a blevel of one or more (as in the demo) and as would be more typical with indexes involved in long running transactions.

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

LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
------- ----------- ---------------
    500           0               0

Note: There are no deleted index entries, none. They’ve already been cleaned out.

A treedump will show the following:

—– begin tree dump

leaf: 0x1402e4a 20983370 (0: nrow: 500 rrow: 500)

—– end tree dump

Note: It only shows 500 rrow and 500 nrow values, clearly highlighting there are no deleted index entries.

A partial index block dump will show the following:

kdxlende 0

The deleted index entry count kdxlende is 0, with no deleted index entries existing in the block. None.

All the deleted index entries have already been cleaned out, with not a subsequent DML operation in sight.

So yes, again Oracle can clean out deleted index entries as part of it’s general processing so that the need to do so manually via an index rebuild, coalesce or shrink is a somewhat rare occurrence.

But wait, there’s still more cases to come when Oracle will simply automatically remove deleted index entries entries …

About these ads

Deleted Index Entries Part IV (Breaking Glass)June 25, 2008

Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes, Oracle Myths.
trackback

Yet another method of cleaning out deleted space Oracle has up its sleeve is the recycling of index blocks that contain nothing but deleted index entries.

In some cases, it’s possible for an index block to contain no current index entries with all the corresponding index entries within the index block having been deleted. The index block may be totally empty of index entries or it may contain just deleted index entries.

Once an index block has no current index entries, Oracle places the block on the segment freelist and is now a candidate block to be recycled and reused elsewhere within the index structure after a subsequent index block split operation.

When recycled, the index block becomes “unattached” from its current location within the logical index structure and is reallocated elsewhere within the logical index structure as the new index block in an index block split operation.

Any previously deleted index entries are removed and the contents of the index block are replaced with new index entries associated with its new logical location within the index structure.

A simple little demo to illustrate this process.

First, I create a simple table and associated index and populate it with a 10000 rows:

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

Table created.

SQL> INSERT INTO test_empty_block SELECT rownum, ‘BOWIE’ FROM dual
CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

SQL> CREATE INDEX test_empty_block_idx ON test_empty_block(id);

Index created.

I next delete the vast majority of the rows, leaving only a handful behind that are likely only found in the last one or maybe two leaf blocks within the index. All the other index leaf blocks therefore only contain nothing but deleted index entries:

SQL> DELETE test_empty_block WHERE id between 1 and 9990;

9990 rows deleted.

SQL> COMMIT;

Commit complete.

If we look at some statistics, we’ll find we have lots of deleted row entries that are all found in leaf blocks that are totally empty, except perhaps the right most leaf block within the index:

SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_blks, del_lf_rows FROM index_stats;

LF_BLKS DEL_LF_ROWS
------- -----------
     21        9990

We next insert a bunch of new rows into the table, but importantly, all these new rows have index entry values that are greater than the previous values. Therefore, all these new index entries will be inserted into the right most side of the index structure and not into the index where we have nothing but the previously deleted index entries.

Oracle will need to allocate new index leaf blocks to accommodate these new index entries, but from where will Oracle get these new index blocks ?

SQL> INSERT INTO test_empty_block SELECT rownum+20000, ‘ZIGGY’
FROM dual CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

If we now look at the index statistics, we notice something very interesting:

SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_blks, del_lf_rows FROM index_stats;

LF_BLKS DEL_LF_ROWS
------- -----------
     21           0

The number of deleted leaf entries has disappeared back to zero and the number of allocated leaf blocks has remained the same at 21.

Oracle has both removed the previously deleted index entries and has also recycled all the previously empty leaf blocks and reused them again to accommodate the new index entries. The index is effectively the same size as it was previously even though we’ve added new values that were greater than the previously deleted values.

So index blocks that are totally empty or contain nothing but deleted index entries become “free” again, are placed on the freelist within the index segment and can be reused or recycled again somewhere else within the logical index structure at some later point in time.

Again, yet another example of Oracle cleaning out these unwanted deleted index entries for us.

However, these empty index blocks can potentially be problematic and can cause performance issues until eventually they actually get reused and recycled.

But that’s a topic for another day.

About these ads

Deleted Index Entries Part V (Trouble)

As discussed in Part IV, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled in subsequent index block split operations. This of course is a good thing as it helps to keep the index as small and efficient as possible without having to perform relatively expensive index maintenance activities such as index rebuilds, coalesces or shrinks.

However, an important issue is that although the effectively empty index block is free and available to be reused, it remains in place in it’s current logical location within the index structure until it’s been recycled. It’s only logically unlinked and re-positioned within the index structure at the time of the block being recycled.

This simple demo highlights this issue.

First create a table and associated index and populate it with a 10000 rows:

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

Table created.

SQL> INSERT INTO test_1 SELECT rownum, ‘BOWIE’ FROM dual CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX test_i ON test_1(id);

Index created.

Next delete most of the rows to so that we have a number of index blocks that contain nothing but deleted index entries which can potentially be subsequently recycled:

SQL> DELETE test_1 WHERE id between 1 and 9990;

9990 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> ANALYZE INDEX test_i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_blks, del_lf_rows FROM index_stats;

LF_BLKS DEL_LF_ROWS
------- -----------
     21        9990

If we now run a select statement that forces the use of the index and which only returns the 10 remaining rows, the high number of logical I/Os highlights how Oracle still accesses the now emptied index blocks during the index range scan operation:

SQL> SELECT /*+ index (test_1) */ * FROM test_1
WHERE id BETWEEN 1 and 10000;

Execution Plan

--------------------------------------------
| Id| Operation                   | Name   |
--------------------------------------------
|  0| SELECT STATEMENT            |        |
|  1|  TABLE ACCESS BY INDEX ROWID| TEST_1 |
|* 2|   INDEX RANGE SCAN          | TEST_I |
--------------------------------------------

Statistics
----------------------------------------------
  0 recursive calls
  0 db block gets
 25 consistent gets
  0 physical reads
  0 redo size
577 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
 10 rows processed

If there’s a significant time lag between index blocks being emptied and subsequently recycled, there could be some scenarios where accessing many of these effectively empty index blocks can be problematic.

Classic case is when we’re interested in the minimum value of a specific column. Oracle can potentially use an index to navigate to the first index leaf block in the index structure to quickly find the minimum value of an indexed column. However, if the first or left-most index leaf block contains no current index entries, Oracle will be forced to navigate to the next leaf block and if it’s also empty, to the next leaf block and so on until it finally comes across the first non-deleted index entry.

Similar demo, but this time lets make the table somewhat bigger for effect:

SQL> CREATE TABLE ziggy (id NUMBER, value VARCHAR2(30)) ;

Table created.

SQL> INSERT INTO ziggy SELECT rownum, ‘BOWIE’ FROM dual CONNECT BY level <=1000000;

1000000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX ziggy_i ON ziggy(id);

Index created.

Let’s now deleted 1/2 the index entries, all those on the left-hand side of the index structure:

SQL> DELETE ziggy WHERE id <=500000;

500000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>’BOWIE’, tabname=>’ZIGGY’, estimate_percent=> null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

We now run the following simply little SELECT statement featuring the MIN function:

SQL> SELECT MIN(id) FROM ziggy;

MIN(ID)
-------
 500001

Execution Plan
--------------------------------------------
| Id| Operation                  | Name    |
--------------------------------------------
|  0| SELECT STATEMENT           |         |
|  1|  SORT AGGREGATE            |         |
|  2|   INDEX FULL SCAN (MIN/MAX)| ZIGGY_I |
--------------------------------------------

Statistics
   0 recursive calls
   0 db block gets
1115 consistent gets
   0 physical reads
   0 redo size
 412 bytes sent via SQL*Net to client
 396 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   1 rows processed

Notice the high numbers of consistent gets, 1115 !! Wow.

Until the empty index blocks get recycled, all those effectively empty index blocks will make this MIN select statement run poorly. Classic example where an index rebuild (or coalesce or shrink) would be benefical to improve the performance of this specific query.

SQL> ALTER INDEX ziggy_i REBUILD ONLINE;

Index altered.

SQL> SELECT MIN(id) FROM ziggy;

MIN(ID)
-------
 500001

Execution Plan
-------------------------------------------
| Id| Operation                  | Name   |
-------------------------------------------
|  0| SELECT STATEMENT           |        |
|  1|  SORT AGGREGATE            |        |
|  2|   INDEX FULL SCAN (MIN/MAX)| ZIGGY_I|
-------------------------------------------

Statistics
  0 recursive calls
  0 db block gets
  3 consistent gets
  0 physical reads
  0 redo size
412 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

Now the consistent gets have dramatically reduced from 1115 to just 3making the select statement run noticeably faster.

Yes, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled by Oracle but until they’ve been reused, they remain linked in place in the index structure. In some scenarios, these empty index blocks can be problematic and is a classic example of when an index rebuild might be beneficial.

About these ads