This is one of the least used Oracle indexes I’ve encountered in a data-warehousing environment. However, a very good instrument in performance problems on OLTPs.
To understand a reverse key index, we must first look at the regular b-tree index on a numerical key, and its storage in the database memory. The index will store, in ascending sort order, all it’s keys on disk. If we consider a key generated by a sequence, then any new value for that key will therefore be inserted in the last database block.
On a high frequency insert setup system in this type of case, you will encounter the buffer busy waits event one too many times.
To see the test on this events, please see post on Buffer Busy Waits – Reverse Key Index – Demo
Now, on considering a reverse key index, the key stored is actually the reversed value. For instance, for values 12, 13, 14 the stored keys will be 21, 31 and 41. This type of index will scatter its consecutive key inserts into multiple blocks, removing the hot-block problem regular keys have.
!!! Please note there is a downside to this approach, meaning the index will no longer allow range scans.
How to create a reverse key index:
create index INDEX_NAME on TABLE_NAME(INDEX_KEY) reverse;
It is also possible to change an existing index, making it reverse:
alter index INDEX_NAME rebuild reverse;
And also to revert a reverse key index into a regular index:
alter index INDEX_NAME rebuild noreverse;
