Creating a reverse key index is very simple. Use the same index creation DDL as usual, but as the following code snippet shows, simply add the reverse keyword at the end. That's it!

One final note: Because there will be intense index insertion in every leaf block, to reduce the frequency of leaf block splitting, consider adding the pctfree 50 storage parameter when creating reverse key indexes.

To give you an idea of the performance impact of using a reverse key index, I created the test situation I described earlier (in "The Situation" section). Over the 120-second test interval, using a nonreverse index, 75% of all wait time was related to buffer busy waits events on an index block with a transaction rate of 0.80 trx/sec. (Each transaction inserted 50,000 rows.) I reran the test with the only difference being the creation of a reverse key index. Over the 120-second test interval, 28% of the wait time was related to buffer busy waits events, with a transaction rate of 0.95 trx/sec. That's great news, because the wait time decreased and the transaction rate increased!