Good practice using reverse indexes on surrogate keys? (Oracle)

Question

Let's say I have a table with an automatically incrementing surrogate key.

Would this be a good example of using a reverse index?

Am I right in saying:

Inserts (in the index) will be faster .. since new values ​​will be inserted randomly, and will not always direct most leaves (constantly causing rebalancing).

Index searches will be slightly slower .. since db would have to spend (a bit) time changing the index.

Since this is a surrogate key .. I would not need the ability to scan a range (which you cannot do with reverse indices).

(Note: I do not use Oracle RAC)

Answer 1

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

In terms of performance, it is much better for you to have one or two hot blocks at any given time that are to be inserted, because it actually ensures that the hot blocks are in the buffer cache, and INSERT does not have to incur the cost of reading the block from disk. If you have inserts that fall into random blocks in the index, the likelihood that the block you want will go out of cache and will bear the cost of physical I / O.

The cost of maintaining a balanced index is quite minimal, but even this contributes to the standard index. If you have a sequence-generated primary key with a normal index, Oracle will make a 90/10 block split in the rightmost block when this block is filled. In contrast, if you have a reverse key index, Oracle should 50/50 block the block whenever the block is full. A 50/50 block splits copies of half of the data from the old block into a new block; splitting into 90/10 blocks only copies the rightmost data value to the new block. Thus, dividing into 90/10 blocks is much cheaper than dividing into 50/50 blocks, and you will need to do approximately the same number of blocks, regardless of the type of index you choose. Thus, the cost of maintaining a regular index is less than the cost of maintaining a reverse key index, even ignoring the effect of the cache.

The reason for using the reverse key index would be that you use RAC, and you want to avoid the cost of many RAC nodes fighting for the same hot block. If you constantly have to send a hot block from one node to another in order to do the next insertion, it may be advisable to use a reverse key index instead to reduce this statement. If you licensed the partitioning option, it would be better to use a hash partitioned index instead (this can be done regardless of whether the tables are partitioned). If you have not licensed the partitioning option, the reverse key index may be good enough to resolve conflicts on the hot block so that you do not require that you license the partitioning.