Good practice to use reverse indexes on surrogate keys? (Oracle)

Question

Say I have a table with an auto-incrementing surrogate key.

Would this be a good case for using a reverse index?

Am I correct in stating:

Insertions (into index) would be faster .. since new values would be inserted randomly, instead of always going to the right most leaf (constantly forcing rebalances).

Index look ups would be marginally slower .. since the db would have to spend (a little) of time reversing the index.

Since it's a surrogate key .. I wouldn't really need range scan ability (which you can't do with reverse indexes).

(Note, I'm not using Oracle RAC)

Answer 1

In general, if you're not using RAC, there would be no reason to use a reverse-key index.

From a performance standpoint, you're much better off having one or two hot blocks at any given point in time that are subject to inserts because that essentially guarantees that the hot blocks will be in the buffer cache and the INSERT won't have to incur the cost of reading the block from disk. If you've got inserts going into random blocks in an index, there is a much greater probability that the block you want would have aged out of the cache and would incur the cost of a physical I/O.

The cost of keeping an index balanced is pretty minimal but even that favors a standard index. If you've got a sequence generated primary key with a normal index, Oracle will do a 90/10 block split on the right-most block when that block fills up. In contrast, if you've got a reverse key index, Oracle has to do 50/50 block splits whenever a given block fills up. A 50/50 block split copies half the data from the old block to the new block, a 90/10 block split only copies the right-most data value to the new block. The 90/10 block split, therefore, is much cheaper than a 50/50 block split and you'd need to do roughly the same number of block splits regardless of the type of index you pick. So the cost of maintaining a regular index is less than the cost of maintaining a reverse key index even ignoring the effect of cache.

The reason you'd consider using a reverse key index would be that you're using RAC and you want to avoid the cost of having many RAC nodes all fighting over the same hot block. If you constantly have to ship the hot block from one node to another in order to do the next insert, it may be worthwhile to use a reverse key index instead to reduce that contention. If you have licensed the partitioning option, it would be better still to use a hash partitioned index instead (this can be done whether or not the tables is partitioned). If you haven't licensed the partitioning option, a reverse key index may be good enough at resolving contention on the hot block to not require that you license partitioning.