jump to navigation

Updates and Indexes Part II (Down Is The New Up) February 9, 2009

Posted by Richard Foote in Index Delete Operations, Oracle Myths, Update Indexes.
8 comments

In Updates and Indexes Part I, I described how there’s no such things as an “update” operation as such on a index and that an update is effectively a delete followed by an insert operation.

I also showed how Oracle only marks index entries as deleted and doesn’t physically delete the index entry at the time of the transaction.

This leads some folk into (incorrectly) thinking indexes that experience lots of update (or delete) operations need to be frequently rebuilt as the delete space might accumulate and waste space within the index structure over time .

However I also showed how deleted space is actually generally automatically reused by Oracle. For example, all it takes is one subsequent tiny little insert operation into a leaf block for all deleted entries within the leaf block to be automatically cleaned out by Oracle.

Therefore just because an index experiences lots of update activity doesn’t necessarily mean the index needs to be rebuilt. Generally, all deleted space is reused and is effectively nothing but free and available space within the index.

To emphasise this key point and attempt to really get the message across, I thought it might be worth going through a little demo of an index that does indeed experience lots of update operations and see what impact it actually has on the index.

Let’s first just create a simple little table that will have two numeric columns:

SQL> create table bowie (id number, value number);

Table created.

Now let’s populate the first column with a monotonically increasing unique identifier and the second column with a random number:

SQL> insert into bowie select rownum, ceil(dbms_random.value(0,50000))
     from dual connect by level <=50000;

50000 rows created.

SQL> commit;

Commit complete.

Now we’ll create an index on the value column that is populated with a bunch of random numbers:

SQL> create index bowie_idx on bowie(value);

Index created.

Let’s collect some statistics and see how much space is being used by this “freshly” created index:

SQL> analyze index bowie_idx validate structure;

Index analyzed.

SQL> select blocks, lf_blks, btree_space, pct_used, del_lf_rows from index_stats;

BLOCKS LF_BLKS BTREE_S PCT_USED DEL_LF_ROWS
------ ------- ------- -------- -----------
   128     110  888032       90           0

Specifically note the number of leaf blocks and the overall btree space usage of the index. Also note that as the index has only just been created, we currently have no deleted index entries.

OK, next we’re going to update the index column with a new random number, one row at a time, for effectively 50% of all rows in the entire table. The table currently has 50,000 rows and we will update every other row (that’s 25,000 rows for those mathematically challenged) with a new random value. Now that will be effectively 25,000 separate delete operations and 25,000 separate insert operations.

There are some folk who would think such an exercise would result in 25,000 deleted index entries which need to be cleaned up at some point via an index rebuild.

There are some folk who would think updating 50% of all index column values would result in so much wasted space that the index would grow in an inefficient manner in order to store all these deleted index entries, likely causing performance issues.

There are some folk who think that such a regular and high proportion of updates on an index column in a table is a clear indication that such an index should be rebuilt on a regular basis.

Well, let’s see what happens. First, let’s update every other row in the entire table with a new random number for the value column:

SQL> begin
  2  for i in 1..25000 loop
  3    update bowie set value = ceil(dbms_random.value(0,50000)) where id = i*2;
  4    commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Let’s see how badly the index has been impacted:

SQL> analyze index bowie_idx validate structure;

Index analyzed.

SQL> select blocks, lf_blks, btree_space, pct_used, del_lf_rows from index_stats;

BLOCKS LF_BLKS BTREE_S PCT_USED DEL_LF_ROWS
------ ------- ------- -------- -----------
   128     110  888032       90          60

How interest !!

The first thing to note is that there aren’t actually 25,000 deleted index entries at all, even though we’ve just updated 25,000 index entries. There are just 60 deleted entries currently in the index. Just 60, that’s it !!

Why ?

Because the index is effectively just a random based index and while we may update (and hence effectively delete) an index entry from a specific leaf block, at some later time we’re likely to insert another index entry into this same leaf block, thereby cleaning out any deleted entries it may contain. Effectively, almost all the deleted index entries are being automatically cleaned out by subsequent random inserts throughout the index structure.

The relative handful of currently marked deleted index entries (60) happen to exist in leaf blocks that have not had a subsequent insert since the last delete operation in the specific leaf block. But even these deleted entries will eventually be cleaned out and the space reused by any other subsequent inserts in the specific leaf blocks.

The deleted space is simply not an issue, the vast majority of it has been cleaned out and reused and those entries that haven’t yet been cleaned out will likely be reused by subsequent insert operations anyways.

If we look at the actual space used by the index, after 50% of all index entries have been updated and we note that the index has not changed at all. It has exactly the same number of leaf blocks and is using exactly the same amount of btree space. (Note: because the index values are random, it’s likely that the new values will not be exactly distributed as it was previously and there might be the possibility that the odd index leaf block could fill and split as it contains more associated values than previously. Regardless, as the index grows with more index entries, this is not going to be an issue anyways).

There is nothing “wrong” or inefficient or fragmented with this index, even though we’ve just updated (and hence deleted) 50% of all its index entries.

With deleted index entries being trivial and likely to be reused at some later time anyways and with the index having the same leaf blocks and btree space as it did when the index was newly created, rebuilding such an index would be a total and utter waste of time and resources.

Again, just because an index column is frequently updated, it doesn’t necessarily mean the index is a candidate for a periodic index rebuilds. Any such suggestions are simply misguided …

Updates and Indexes Part I (Fashion) January 17, 2009

Posted by Richard Foote in Index Delete Operations, Oracle Indexes, Oracle Myths, Update Indexes.
27 comments

Let’s start with a few basic concepts.

Simplistically, Oracle doesn’t generally care where or in what order data is stored in heap tables, unless the table is partitioned or clustered. Therefore, when an update operation is performed on a column value, Oracle can basically make the change “in place” within the table block. If the value of a name column changes from say “BOWIE” to a new value of “FOOTE”, Oracle can simply just make the necessary change to the specific table block. Only if the new value is larger and there’s not sufficient space within the current table block for the new value does the picture get a little more complicated, with Oracle having to migrate the row.

However, the story isn’t quite so simple for corresponding changes to index column values. The key difference (no pun intended) is that unlike most heap tables, the location and order of the data within the index is very much an issue. All index entries must be and must always be in the order of the indexed columns. Otherwise, it would be impossible for Oracle to efficiently navigate down the index structure to find within the index all the  necessary indexed values required for an index range scan. Imagine for one moment how difficult it would be to find someones phone details if the telephone book wasn’t ordered on names or to use an index in the back of a reference book if the index wasn’t ordered.

The order of an index is crucial and must always be in the order of the indexed column values. Therefore, if a specific indexed value of  say “BOWIE” is updated to a new value of “FOOTE”, Oracle can’t simply make the change “in place” within the leaf block as this would result in the index order no longer being maintained. The new value of “FOOTE” would be surrounded by index values starting with “B” and there would be no easy way to subsequently find it within the index. It would likely not even be in the same index leaf block as those other index entries beginning with “F” where the new value should actually reside.

Therefore, Oracle doesn’t actually “update” an index value. The old index entry is marked as deleted and the a new index entry is inserted in the appropriate location within the index to ensure the index order is maintained. So the update of an index value is effectively a delete operation followed by an insert.

Simple example to demonstrate. Let’s first create a little table and index.

SQL> create table test_update (id number, name varchar2(10));

Table created.

SQL> create index test_update_idx on test_update (name);

Index created.

Now, let’s insert one row.

SQL> insert into test_update values (1, ‘BOWIE’);

1 row created.

SQL> commit;

Commit complete.

Let’s have a quick look at a block dump of our little index. As the index only contains the one index entry, the index will only consist of a single leaf block. This specific block is located next to the segment header of the index.

SQL> select header_file, header_block from dba_segments where segment_name = ‘TEST_UPDATE_IDX’;

HEADER_FILE HEADER_BLOCK
----------- ------------
          7       118537

We just need to add 1 to the header_block to get us the specific block id we need to dump.

SQL> alter system dump datafile 7 block 118538;

System altered.

Following is the portion of interest from the index block dump:

Leaf block dump
===============
header address 425361500=0x195a805c
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0×26
kdxcofeo 8021=0x1f55
kdxcoavs 7983
kdxlespl 0
kdxlende 0
kdxlenxt 0=0×0
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: ——, lock: 2, len=15
col 0; len 5; (5):  42 4f 57 49 45
col 1; len 6; (6):  01 c1 ce 8a 00 00

—– end of leaf block dump —–

We note for now that the index indeed just has the one index entry (with a row# 0).

Let’s now update this indexed column and see what impact this has in the index.

SQL> update test_update set name = ‘ZIGGY’ where id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 7 block 118538;

System altered.

New block dump follows:

Leaf block dump
===============
header address 425361500=0x195a805c
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0×28
kdxcofeo 8006=0x1f46
kdxcoavs 7966
kdxlespl 0
kdxlende 1
kdxlenxt 0=0×0
kdxleprv 0=0×0
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 c1 ce 8a 00 00
row#1[8006] flag: ——, lock: 2, len=15
col 0; len 5; (5):  5a 49 47 47 59
col 1; len 6; (6):  01 c1 ce 8a 00 00
—– end of leaf block dump —–

We notice of few interesting things. Firstly, even though we’ve only ever inserted the one row in the table, the index now actually has two index entries (note the index row count kdxconro value has increased from 1 to 2 and we can see two actual index entries, row#0 and row#1).

Another interesting point is that the initial index entry has been marked as deleted (via the “D” flag highlighted in blue) and that the deleted row count kdxlende value has gone up to 1.

So yes indeed, an update index operation actually consists of a delete operation followed by an insert, even if the new index value were to reside in the same index leaf block as the previous value. Note the deleted index entry isn’t actually physically deleted, it’s only marked as deleted and so continues to consume space within the index leaf block.

If we look at the index_stats for this index:

SQL> analyze index test_update_idx validate structure;

Index analyzed.

SQL> select del_lf_rows from index_stats …

DEL_LF_ROWS
-----------
          1

We notice that we do indeed have a deleted index entry listed in the statistics.

Now it’s at this point of the story when some people go “Aaah haa !! So if we have lots of updates, we effectively have lots deletes and we therefore have lots of deleted space that wastes space within the index. Therefore we would need to periodically rebuild such an index as the deleted space will just continue to grow, making the index larger and less efficient over time”.

Not necessarily.

The point that many don’t quite understand is that this “wasted” delete space can be subsequently reused by Oracle. It’s just free space that in the vast majority of indexes is automatically cleaned out and reused by Oracle.

To illustrate, let’s now insert a second row into this table. Notice the new row has an indexed value that is completely different to the previous values but because we still have available space in our current index leaf block, Oracle will simply insert the new value in this leaf block as well.

SQL> insert into test_update values (2, ‘PINK FLOYD’);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 7 block 118538;

System altered.

Let’s have a look at our block dump now …

Leaf block dump
===============
header address 425361500=0x195a805c
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0×28
kdxcofeo 7986=0x1f32
kdxcoavs 7961
kdxlespl 0
kdxlende 0
kdxlenxt 0=0×0
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[7986] flag: ——, lock: 2, len=20
col 0; len 10; (10):  50 49 4e 4b 20 46 4c 4f 59 44
col 1; len 6; (6):  01 c1 ce 8a 00 01
row#1[8006] flag: ——, lock: 0, len=15
col 0; len 5; (5):  5a 49 47 47 59
col 1; len 6; (6):  01 c1 ce 8a 00 00
—– end of leaf block dump —–

Now isn’t that interesting. The previously marked deleted index entry has disappeared. It’s been automatically cleaned out by Oracle and the deleted row count value kdxlende is now back to 0

We only have an index row count  kdxconro value of 2 for the two actual index entries corresponding to the two rows in the table. The previously deleted index entry is no longer recorded or stored in any way within the index.

When the new index entry was inserted, Oracle basically needed to reorganise the index leaf block to accommodate for the new index entry and automatically cleaned out any deleted index entries there may have been in the leaf block in the process. That’s all it takes to clean out deleted index entries from an index leaf block, just one subsequent insert in the leaf block.

If we now look at the index_stats …

SQL> analyze index test_update_idx validate structure;

Index analyzed.

SQL> select del_lf_rows from index_stats;

DEL_LF_ROWS
-----------
          0

We note that indeed, no deleted index entries are now stored within the index. Periodically rebuilding such an index may not be necessary after all …

Regular readers will recall I’ve previously discussed how deleted index entries are generally cleaned out by Oracle automatically. Sometimes, it’s worth hammering the point a few times ;)

More on the topic of update operations and indexes to come …

Empty Leaf Blocks and Statistics (Sense Of Doubt) July 8, 2008

Posted by Richard Foote in Index Access Path, Index Block Splits, Index Delete Operations, Index statistics, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning.
14 comments

I’ve recently been discussing how empty index blocks or those blocks that contain nothing but deleted index entries are placed on the index freelist and can potentially be recycled during subsequent index block split operations.

A point that’s not so well known about such empty index blocks is how Oracle considers them when calculating index related statistics and the possible implications this may have on the CBO.

Let’s set the scene with an example I’ve used previously where we load a table/index with 10000 entries and then subsequently delete the vast majority of them.

SQL> create table rich as select rownum id, ‘Bowie’ text from dual connect by level <= 10000;
 
Table created.
 
SQL> create index rich_i on rich(id);
 
Index created.

OK, so we now have an index with 10000 entries. Let’s just check to see how many leaf blocks we currently have:

SQL> analyze index rich_i validate structure;

Index analyzed.

SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;

   LF_ROWS    LF_BLKS DEL_LF_ROWS
---------- ---------- -----------
     10000         21           0

So we currently have 10000 LF_ROWS and 21 LK_BLKS with no deleted index rows at this stage.

Let’s now deleted the vast majority of rows from the table and hence index row entries from the index:
SQL> delete rich where id <= 9990;
 
9990 rows deleted.
 
SQL> commit;
 
Commit complete.

OK, so now we have an index with the vast majority of the index entries having been deleted and with all but one index leaf block effectively empty.

Let’s start by looking at how the ANALYZE INDEX … VALIDATE STRUCTURE deals with empty leaf blocks and index entries:

SQL> analyze index rich_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;

   LF_ROWS    LF_BLKS DEL_LF_ROWS
---------- ---------- -----------
     10000         21        9990

The first thing we notice is that the LF_ROWS statistics still has a value of 10000. It still counts index entries, even if they’ve been deleted.

We also notice that the LF_BLKS value is 21 so those leaf blocks that are effectively empty are still counted as well.

Let’s now collect statistics using DBMS_STATS as currently recommended by Oracle:

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

If we now look at the index statistics:

SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘RICH_I’;

INDEX_NAME NUM_ROWS LEAF_BLOCKS
---------- -------- -----------
RICH_I           10           1

We notice a couple of important differences. Firstly, the NUM_ROWS value is 10, highlighting that only non-deleted index entries are counted. We also notice that the number of LEAF_BLOCKS is only 1, highlighting that only those index leaf blocks that contain non-deleted index entries are counted. Although there are 20 other leaf blocks within the index structure, these are not counted and considered by the CBO when statistics are calculated using DBMS_STATS.

If we run the following simple little query that effectively selects all remaining rows from the table, we notice the following execution plan:

SQL> select * from rich where id between 1 and 10000;

        ID TEXT
---------- -----
      9991 Bowie
      9992 Bowie
      9993 Bowie
      9994 Bowie
      9995 Bowie
      9996 Bowie
      9997 Bowie
      9998 Bowie
      9999 Bowie
     10000 Bowie

Execution Plan
--------------------------------------------
|Id | Operation                   | Name   |
--------------------------------------------
| 0 | SELECT STATEMENT            |        |
| 1 |  TABLE ACCESS BY INDEX ROWID| RICH   |
|*2 |   INDEX RANGE SCAN          | RICH_I |
--------------------------------------------

The index is actually used to select all the remaining 10 rows, in part because the index related costs are so low.

Let’s see what would happens if we were to use the old, ANALYZE command to calculate the index statistics:

SQL> analyze index rich_i compute statistics;

Index analyzed.

First, let’s see if the index statistics are any different …

select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘RICH_I’;

INDEX_NAME NUM_ROWS LEAF_BLOCKS
---------- -------- -----------
RICH_I           10          21

OK, a big big difference here. Where previously, DBMS_STATS didn’t include the empty leaf blocks in it’s statistics, we now notice that using the ANALYZE command does include such empty leaf blocks. The LEAF_BLOCKS value is now 21, not 1 as it was previously. Note though that the number of NUM_ROWS is still 10, so it still doesn’t count the deleted index entries themselves, just the empty leaf blocks.

But leaf blocks is one of the key statistics used by the CBO when calculating the cost of using an index related access path. Could this all make a difference in how our previous query is costed by the CBO ?

SQL> select * from rich where id between 1 and 10000;

        ID TEXT
---------- -----
      9991 Bowie
      9992 Bowie
      9993 Bowie
      9994 Bowie
      9995 Bowie
      9996 Bowie
      9997 Bowie
      9998 Bowie
      9999 Bowie
     10000 Bowie

10 rows selected.

Execution Plan
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| RICH |
----------------------------------

Oh yes indeed. Now the CBO has decided to use a Full Table Scan, in large part because of the additional calculated costs associated with using the index.

Note these tests work the same on all supported versions of Oracle.

So empty leaf blocks can still have a large impact on not only how a query may perform but indeed on how the CBO calculates the associated costs, depending on how the statistics are generated.

Yes, there are differences between the ANALYZE command and DBMS_STATS. This is one of the more subtle differences …

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.

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

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

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.

Deleted Index Entries Part II (V-2 Schneider) June 18, 2008

Posted by Richard Foote in Index Delete Operations, Oracle Indexes.
17 comments

I’m back !!

In Part I, we looked at how index entries are only marked as deleted during delete/update operations. This means the DML operation and resultant transaction doesn’t have to concern itself with physically cleaning out the deleted entries.

The question is therefore, are these deleted index entries “deadwood”, wasted space that makes the index less compact and less efficient over time requiring a potential periodic index rebuild, or can this deleted space be subsequently reused by Oracle ?

There’s a common perception that deleted space within an index is indeed “deadwood” or can only be reused in very specific circumstances, such as when an identical index value is subsequently re-inserted.

However, this is just another of Oracle’s infamous urban myths. In the vast majority of cases, deleted space within an index can indeed be recycled and can indeed be subsequently reused. Today, I’m just going to begin by focusing on the most common method by which deleted index entries are generally cleaned up.

All it takes to clean out all the deleted index entries within a specific index leaf block is a subsequent insert in the block. That’s it, that’s all it takes. Just one new index entry in a leaf block will automatically clean out all associated deleted index entries that may currently exist within the block. Even if there are hundreds of deleted entries with the leaf block, just the one new index entry will clean them all out. Note the new index value doesn’t have to be the same as any of the deleted index entries, it doesn’t even have to be within the range of any of the deleted index entries within the block. Any new index entry within the leaf block will do the job quite nicely.

A very simple demonstration I use to highlight this point. First, create a simple table and associated index with 10 rows.

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

10 rows created.

SQL> COMMIT;

Commit complete.

Next, deleted say 4 of the rows from the table.

SQL> DELETE del_stuff WHERE id in (2,4,6,8);

4 rows deleted.

SQL> COMMIT;

Commit complete.

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

LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE
------- ----------- --------------- -------
     10           4              56     140

 

Note: It clearly shows 4 index entries are indeed currently marked as deleted.

 

A treedump will show the following:

 

—– begin tree dump

leaf: 0x1402e3a 20983354 (0: nrow: 10 rrow: 6)

—– end tree dump

 

Note: It only shows 6 rrow but 10 nrow values, clearly suggesting there are currently 4 deleted index entries.

 

A partial index block dump will show the following:

 

kdxlende 4

 

row#1[7928] flag: —D–,lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 40 2e 32 00 01

 

That indeed there are currently 4 index entries marked as deleted with the ‘D’ flag within the index block.

 

However, just a single subsequent insert will clean out all 4 of these deleted index entries. Note the new value (100) is not the same value as any of the previously deleted entries and is not even within the range of previously deleted index entries:

 

SQL> INSERT INTO del_stuff VALUES (100, ‘New Row’);

 

1 row created.

 

SQL> COMMIT;

 

Commit complete.

 

Now, if we look at the same leaf block, we find:

 

LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE
------- ----------- --------------- -------
      7           0               0      98

 

That indeed, INDEX_STATS no longer has any statistics of deleted index entries.

 

—– begin tree dump
leaf: 0x1402e3a 20983354 (0: nrow: 7 rrow:  7)
—– end tree dump

 

That the Index Tree dump no longer has any record of the deleted index entries.

 

That the index block dump no longer has any record of the deleted index entries and that:

 

kdxlende 0

 

the count of deleted index entries within the block has been reset to 0.

 

For most randomly inserted indexes, this means deleted entries will be eventually automatically cleaned out and the freed space reused by subsequent insert operations. Worrying about deleted space and rebuilding such indexes is typically unnecessarily.

 

I’ll next discuss another method by which Oracle will clean out and remove deleted index entries from its indexes …

 

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.
9 comments

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

0×01   0×0000.000.00000000  0×00000000.0000.00  —-      0  fsc 0×0000.00000000

0×02  0×0008.024.0000075b  0x00804e29.0078.0b  –U-      1  fsc 0×0011.00000000

  ……

kdxlende 1

kdxlenxt 0=0×0

kdxleprv 0=0×0

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 …

Follow

Get every new post delivered to your Inbox.

Join 1,713 other followers