A reverse-key index is B*Tree index where the bytes of each indexed column
stored in a reverse order. They are very useful for Oracle Parallel Server
where contention for the same leaf blocks during data inserts can cause
inter-instance "pinging", excessive I/O and performance degradation.
Reversing index keys allows insertions to be distributed evenly across
all the leaf keys in the index.
But reverse-key indexes do not allow index range scan since lexically
adjacent keys are not stored next to each other, so you probably should not
use reverse-key indexes unless you are in OPS environment.
I would add something to inosov's explanations, which BTW are absolutely correct.
Reverse key indexes are not suitable only for OPS configuration. They can also be very benefitial for normal instances, where rows are frequently added with uniformly increasing values on indexed columns. This typicaly occures with surogate primary keys, generated by sequences. If the transaction rate is very high for such tables, a single index leaf block (holding the latest values generated from sequence) will become a "hot spot", because every inserted row will require an entry in the same leaf block (untill it becomes full, when the next leaf block will become a hot spot). By reversing the bytes in the index entry the load is evenly spread among wider range of leaf blocks.
But be carefull with reverse indexes. As inosov pointed out, they can't be used in range scans. So use them only if you select from this table with equality operators.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
I understand what's been talked about in this thread except:
"They are very useful for Oracle Parallel Server
where contention for the same leaf blocks during data inserts can cause inter-instance "pinging", excessive I/O and performance degradation."
What is inter-instance pinging? Why does it give excessive I/O?