Updates and Indexes

Oracle doesn’t generally care where or in what order the data is stored in the “Heap” table, therefore when an update operation is performed on a column_value, oracle can basically make changes “in place” with in the  table block. If the value of the column changes from “HELLO” to a new value of “WORLD”, oracle can simply make the necessary changes to the specific table block. Only if the new value is larger and there is no sufficient space available within the current table block then picture get more complicated with oracle having to migrate the row.

However the story is completely different for the corresponding changes to the index column values. The key difference is that unlike the “Heap” table the location and order of the data in the index is very much an issue. All the index entries must and always be in order of the indexed columns. Otherwise it would be impossible for oracle to efficiently navigate down the index structure to find with in the index all the necessary indexed values for a range scan operation.

Therefore Oracle doesn’t actually “update” an index value. The old index entry is marked as deleted and the new index entry inserted into 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 operation.

Simple example to demonstrate this,

rajesh@ORA12C> create table t(x int,y varchar2(20));

Table created.

rajesh@ORA12C> create index t_idx on t(y);

Index created.

rajesh@ORA12C> insert into t(x,y) values(1,'Hello');

1 row created.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C> analyze index t_idx validate structure;

Index analyzed.

rajesh@ORA12C> select lf_rows,del_lf_rows from index_stats;

   LF_ROWS DEL_LF_ROWS

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

         1           0

1 row selected.

rajesh@ORA12C>

The index consist of one index entry and no deleted entries within it.

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

rajesh@ORA12C> update t set y ='World' where x =1;

1 row updated.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C> analyze index t_idx validate structure;

Index analyzed.

rajesh@ORA12C> select lf_rows,del_lf_rows from index_stats;

   LF_ROWS DEL_LF_ROWS

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

         2           1

1 row selected.

rajesh@ORA12C>

we notice a few interesting things, firstly even though we have only inserted one row in the table, the index now has two index entries (lf_rows=2), another interesting point is the initial index entry has been marked as deleted (del_lf_rows=1) and del_lf_rows value gone up to 1.

So yes the update index operation is actually consist of a delete operation followed by an insert, even if the new value were to reside in the same index leaf block as the previous value. Note the deleted index entry isn’t physically deleted, instead it is marked as deleted, so continue to consume the space within the index structure.

Now it is this point of confusion that some people say, if we have lots of updates (on the indexed column) we effectively have lots of deletes and we therefore have lots of deleted space that wastes space within the index. Therefore we need periodically rebuild such index as the deleted space will just continue to grow, making the index larger and less efficient over the time.

Not necessarily, the point that many don’t 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 in the table, the new row has an indexed value that is completely different from the previous value, since we have sufficient space with the current leaf block, oracle will just insert this new value into this leaf block as well.

rajesh@ORA12C> insert into t(x,y) values(2,'Adams');

1 row created.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C> analyze index t_idx validate structure;

Index analyzed.

rajesh@ORA12C> select lf_rows,del_lf_rows from index_stats;

   LF_ROWS DEL_LF_ROWS

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

         2           0

1 row selected.

rajesh@ORA12C>

Now, the previously delete index entry has disappeared. It has been automatically cleaned out by oracle and del_lf_rows has become zero. When new index entry as inserted, oracle basically need to reorganize the index leaf block to accommodate for the new index entry and automatically cleaned out any deleted index entry that may have been in the leaf block.

That’s is all it takes to clean out deleted index entries from an indexed leaf block, just one subsequent insert into that leaf block. Periodically rebuilding such an index may not necessary after all.