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!

The bad news is while resolving the buffer busy wait issue, we could have significantly impacted query performance. Suppose we optimized a query based on a nonreversed sequence number column index. But suppose because of buffer busy waits, we dropped the nonreversed key index and created a reversed key index. Now those nicely ordered sequence numbers are scattered all over the index leaf blocks. That high-performing index range scan may not perform so well now.9 In fact, the cost-based optimizer (CBO) should recognize this situation and devise another execution plan strategy. Otherwise, the query could potentially touch every index leaf block and also a large number of data blocks!

So be aware that while reverse key indexes can increase insert concurrency and throughput, they can also have a severe negative impact on previously tuned queries. As a performance analyst, your job will be to find a performance compromise or implement a creative solution (like partitioning the index or the data) to maximize performance for both insert and select operations.