Oracle Database - Reverse Key Indexes

Card Puncher Data Processing

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.

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.





Discover More
Card Puncher Data Processing
Oracle Database - (B|Balanced) Tree - BTree indexes

The implementation of btree index in the Oracle database. To get to the leaf block to retrieve the first row of a query of the form will take the same number of I/Os regardless of the...



Share this page:
Follow us:
Task Runner