jump to navigation

Deleted Index Entries Part V (Trouble) July 1, 2008

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

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 3 making 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

Comments»

1. Brian Tkatch - July 1, 2008

So, with empty blocks its either rebuild or re-use?

2. Richard Foote - July 1, 2008

Hi Brian

Or you can just re-insert index entries back again that logically live in the location where the index block currently exists within the index structure and clean out the deleted data before the index block is recycled.

3. Rick Stephenson - July 2, 2008

In part IV you said “Once an index block has no current index entries… At this point the index block becomes “unattached” from its current location”, now you say “index blocks that contain nothing but deleted index entries…they remain linked in place in the index structure”. These seem like contradicting statements. What am I misunderstanding?
Thanks,
Rick

4. Richard Foote - July 3, 2008

Hi Rick

In your quotes above you miss out the critical part (the … ) where I then go on to mention how blocks get reused and recycled. It’s at this point that I was trying to refer to when the block is unattached from the logical index structure.

However, if you mis-understood, then others may misunderstand as well so I’ve modified the piece to make this point clearer.

Thanks.

5. Rick Stephenson - July 3, 2008

Thanks for the clarification. I appreciate your writings, they are very helpful.

6. Gabor Kecskemeti - July 8, 2008

Hi Rick,

Wouldn’t COALESCE solve this issue too? I ran your example with ALTER INDEX ziggy_i COALESCE; and it gave the same result. And COALESCE is a ‘cheaper’ operation than rebuild regarding resources, and it won’t touch the existing index structure, just the empty blocks, which is a big plus for a heavily used index. (You won’t have to go through all the initial block splits caused by inserts after a build / rebuild.)

7. Gabor Kecskemeti - July 8, 2008

Umm.. meant “Hi Richard” above, sorry :)

8. Richard Foote - July 8, 2008

Hi Gabor

Yes absolutely, Coalesce (and Shrink) would both solve the issue as well.

However, it’s not necessarily always true that Coalesce is a “cheaper” operation, it depends as I’ve previously discussed:

http://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/

Also, it depends somewhat on how you set your pctfree regarding how problematic subsequent block splits will be or within what timeframe after a rebuild.

BTW, I’ve been called a lot worse than Rick, although in one example it does indeed rhyme with Rick :-)

9. At least one case where rebuilding indexes helps… | AppCrawler - August 26, 2009
10. Index entries | Kiibouyanrunlun7 - May 30, 2011

[...] Deleted Index Entries Part V (Trouble) « Richard Foote’s OracleAs 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. [...]

11. john - December 22, 2012

Hi Richard,

First of all, Thank you very much for providing lot of information.

I am going through the series “Deleted Index Entries”. In Part II, you shown us the example where, after deleting 4 records and inserting 1 record, index doesn’t have any “statistics” on deleted index entries(shows 0). Though internally we haven’t inserted remaining 3 records. What does it mean to Oracle? From the example shown in this article(part V) it indeed read the deleted index entries? Does it show somewhere in index_stats table?

Also, we have a large table(around 300M records,35GB Size) with 5 indexes(each being <30GB size). We are archiving this table and deleting almost 250M records. Since we archive the old data daily, table remains at around the same volume(50M) in future as well. I believe this is the right candidate for rebuilding indexes? Please suggest.

12. Richard Foote - February 7, 2013

Hi John

When you delete say 4 index entries from a leaf block, they initially just get marked as deleted, they’re not physically cleaned out of the index. However, when you next insert an index entry into the leaf block in another transaction, even if it’s just the one index entry, as part of updating the leaf block, Oracle will at this time physically clean out any previously deleted entries within the block. So the previously deleted entries are now no longer recorded and were really nothing more than free space waiting to be reused.

If you insert into the table daily roughly the same volume you delete, then providing the inserts go into the same leaf blocks as the deletes or the deletes totally empty out a leaf block, then the deleted space will likely get reused and there’s no need to rebuild. You can easily monitor and likewise see if the indexes also remain roughly the same size.

13. Yili Li - March 21, 2013

Hi Richard,
This is a very impressive post and thanks so much for sharing this knowledge…
As you mentioned sometimes until the empty index blocks get recycled, all those effectively empty index blocks will make query statement run poorly.
So the way empty blocks get recycled are rebuild/reuse(re-insert index entries back)…
There is noway Oracle could recycle the empty block by itself automatically?
Thanks,

Richard Foote - April 30, 2013

Hi Yili

No. If you delete a whole bunch of rows that result in effectively empty leaf blocks, then these will remain in the index structure and will not be “recycled” automatically until more rows get inserted/updated or the index is explicitly altered (rebuild, coalesce, shrink).

So if an index has significant delete activity AND there is to be no further activities for a significant period of time, then some form of intervention might be required.


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,870 other followers

%d bloggers like this: