Unique and non-unique index

There is no such thing as non-unique index entry as such as Oracle ensures all index entries are effectively unique by adding the rowid to the index key for all non-unique index. Fundamentally this is required since Oracle need some way of effectively finding the precise index entry associated with an update or delete operation. Without having the rowid as part of the index key, Oracle would be forced to navigate to the first occurrence of an index value and search through all the occurrence of the index value until it find the specific entry containing the rowid of interest.  This could result in visiting many leaf blocks potentially if the index value spans over multiple leaf blocks. By including the rowid as the last index key column, non-unique index values are further ordered based on the rowid within the same indexed values. Oracle can therefore navigate directly to the leaf block containing the exact index entry of interest as the rowid can be included in the branch block to determine both the index entry and the rowid ranges found in the leaf block.

If we look at a simple example by creating one table with a non-unique index:

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

Table created.

rajesh@ORA12C> insert into t values(1,'Hello_World');

1 row created.

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

Index created.

rajesh@ORA12C> analyze index t_idx validate structure;

Index analyzed.

rajesh@ORA12C> select name,blocks,lf_rows,del_lf_rows from index_stats;

NAME           BLOCKS    LF_ROWS DEL_LF_ROWS

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

T_IDX               8          1           0

1 row selected.

rajesh@ORA12C>

Now, if I were to delete and subsequently re-insert a row in the table with same index value within a single Transaction, note that the rowid of the new row will differ from the deleted row. Therefore we need a different index entry for the new index row because if the rowid differs then the associated index entries must differ as well.

Therefore if we were to delete and re-insert the same index value with in a single transaction, Oracle is force to create a new index entry and will not reuse the existing, deleted index entry.

rajesh@ORA12C> delete from t ;

1 row deleted.

rajesh@ORA12C> insert into t values(1,'Two');

1 row created.

rajesh@ORA12C> analyze index t_idx validate structure;

Index analyzed.

rajesh@ORA12C> select name,blocks,lf_rows,del_lf_rows from index_stats;

NAME           BLOCKS    LF_ROWS DEL_LF_ROWS

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

T_IDX               8          2           1

1 row selected.

rajesh@ORA12C>

We notice now the previous index entry has been logically deleted (del_lf_rows=1) and Oracle has created a new index entry with the new associated rowid.

Now let us run the same demo again, but this time with a unique index instead of non-unique index.

rajesh@ORA12C> truncate table t ;

Table truncated.

rajesh@ORA12C> drop index t_idx;

Index dropped.

rajesh@ORA12C> insert into t values(1,'Hello_World');

1 row created.

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

Index created.

rajesh@ORA12C> analyze index t_idx validate structure;

Index analyzed.

rajesh@ORA12C> select name,blocks,lf_rows,del_lf_rows from index_stats;

NAME           BLOCKS    LF_ROWS DEL_LF_ROWS

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

T_IDX               8          1           0

1 row selected.

rajesh@ORA12C>

Note the biggest difference here is Index has been defined as unique, all associated index entries must be unique. It is simply not possible to have duplicate index entries within a unique index structure. Therefore is not necessary to have rowid as a separate column of the index entry as the index values themselves are sufficient to uniquely identify each and every index entry.

If we were to now delete and re-insert the same indexed value within a single transaction, Oracle can now reuse the same, deleted index entry, because the index entry is identical to the deleted one. The only possible difference is the rowid, but the rowid is no longer the part of the indexed key column (like a non-unique index)

Therefore if we were do delete and re-insert the same index value with in a single transaction, Oracle don’t need to create a new index entry and can simply reuse the existing deleted index entry.

rajesh@ORA12C> delete from t ;

1 row deleted.

rajesh@ORA12C> insert into t values(1,'Two');

1 row created.

rajesh@ORA12C> analyze index t_idx validate structure;

Index analyzed.

rajesh@ORA12C> select name,blocks,lf_rows,del_lf_rows from index_stats;

NAME           BLOCKS    LF_ROWS DEL_LF_ROWS

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

T_IDX               8          1           0

1 row selected.

rajesh@ORA12C>

Deleted space can be reused (provided index key values are same) with in a single transaction in a unique index, but not in a non-unique index. Deleted space with in a non-unique index can only be reused by the subsequent transaction.