Oracle Database - Reverse Key Indexes

> Database > Oracle Database

1 - About

They are B*Tree indexes whereby the bytes in the key are “reversed”. Reverse key indexes can be used to obtain a more even distribution of index entries throughout an index that is populated with increasing values.

3 - Example

For example, by using a sequence to generate a primary key, the sequence will generate values like :

  • 987500,
  • 987501,
  • 987502,
  • and so on.

These values are sequential, so if I were using a conventional B*tree index, they would all tend to go to the same right-hand-side block, thus increasing contention for that block.

With a reverse key index, Oracle will logically index :

  • 005789,
  • 015789,
  • 205789,
  • and so on.

Oracle will reverse the bytes of the data to be stored before placing them in the index, so values that would have been next to each other in the index before the byte reversal will instead be far apart.

This reversing of the bytes spreads out the inserts into the index over many blocks.

Advertising