Asynchronous Global Index Maintenance 12c - Part III

There’s actually no such thing as a 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 indexes. Fundamentally, this is essential because Oracle needs some way of efficiently 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 occurrences of the index value until it finds the specific entry containing the rowid of interest. This could potentially result in visiting many leaf blocks if the index value spans multiple leaf blocks. By including the rowid as the last index key column, non-unique index values are further ordered based on the corresponding rowid within the same indexed values. Oracle can therefore always navigate directly to the leaf block containing the exact index entry of interest as the rowid can be included in the branch blocks to determine both the index entry and rowid ranges found in specific leaf blocks

Now if we were to delete and subsequently re-insert a row in the table with same index value within a single transaction, note the rowid of the new row by definition will differ from the deleted row. Therefore, we would need a different index entry for the new index row because if the rowids differ, then the associated index entries must differ as well, Therefore, if we were to delete and re-insert the same index value within a single transaction, Oracle is forced to create a new index entry and will not reuse the existing, deleted index entry

In  unique index. It’s simply not possible to have duplicate index entries within a Unique index structure. Therefore, it’s not necessary to have the rowid as a separate column of the index entry as the index values themselves are sufficient to uniquely identify each and every index entry. The rowid is basically just another piece of overhead associated with the index entry rather than a separate index column

If we were to now delete and re-insert the same index value within a single transaction, Oracle can now reuse the same, deleted index entry, because the index entry is effectively identical to the deleted one. The only possible difference is the rowid but the rowid is no longer a part of the index column list and so can just be updated as necessary.

The same happens with Global unique index Maintenance, when the same key values reaches the index structure delete orphan entries get reused.

rajesh@PDB1>
rajesh@PDB1> create table t
  2  partition by list(x)
  3  (
  4     partition p1 values (1),
  5     partition p2 values (2),
  6     partition p3 values (3),
  7     partition p4 values (4),
  8     partition p5 values (5)
  9  )
 10  nologging as
 11  select b.*, 1 as x,rownum as y
 12  from big_table b
 13  where rownum <= 1000 ;

Table created.

rajesh@PDB1> create unique index t_idx on t(owner,object_type,object_name,y) nologging;

Index created.

rajesh@PDB1>
rajesh@PDB1> alter table t drop partition p1 update global indexes ;

Table altered.

rajesh@PDB1> analyze index t_idx validate structure;

Index analyzed.
rajesh@PDB1>
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;

NAME                                                  LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX                                                    1000        1000

1 row selected.
rajesh@PDB1> alter table t add partition p1 values (1);

Table altered.
rajesh@PDB1> insert into t
  2  select b.*, 1 as x,rownum as y
  3  from big_table b
  4  where rownum <= 1000;

1000 rows created.
rajesh@PDB1> commit;

Commit complete.
rajesh@PDB1>
rajesh@PDB1> analyze index t_idx validate structure;

Index analyzed.
rajesh@PDB1> select name,lf_rows,del_lf_rows from index_stats;

NAME                                                  LF_ROWS DEL_LF_ROWS
-------------------------------------------------- ---------- -----------
T_IDX                                                    1000           0

1 row selected.
rajesh@PDB1>
rajesh@PDB1>