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:
14 comments

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.

Oracle Mix: Time Running Out To Vote For OOW 2008 Sessions (Time Will Crawl) June 28, 2008

Posted by Richard Foote in Oracle Mix.
Tags:
add a comment

Just a quick note to say time is running out for anyone interested in suggesting or nominating for a session at this year’s Oracle OpenWorld via Oracle Mix. Nominations close this coming Monday, 30 June 2008, so you don’t have much time left if you wish to nominate a presentation that might be of interest to folks attending OOW 2008. If you think you have something interesting to contribute, go on, give it a go !!

Voting closes on Sunday 13 July 2008. If you want to have a say and help select sessions that would be of interest to you at OOW 2008, this is a great opportunity to have direct input. The most popular 35 sessions as voted by you, the Oracle community, will be selected and will be on this year’s schedule at OOW 2008.

I’ve decided to nominate 3 sessions, all of which are currently in the top 35 (Note: you need an oracle.com account to view the sessions, to nominate or to vote):

5) Indexing Secrets: https://mix.oracle.com/ideas/27859-indexing-secrets-with-richard-foote

23) Bitmap Index Interanls: https://mix.oracle.com/ideas/27926-bitmap-index-internals-with-richard-foote

30) Index Organized Tables Internals: https://mix.oracle.com/ideas/27870-index-organized-tables—the-internals-with-richard-foote

If you’re planning on attending OOW this year and my sessions or any of the many other interesting nominated sessions are of interest to you, then I encourage you to vote and have a say in what will be on the agenda.

I think this is a great initiative by Oracle but it’s up to you to make it a successful one :)

Follow

Get every new post delivered to your Inbox.

Join 1,808 other followers