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>
