在代理键上使用反向索引的好习惯？ （Oracle）的 [英] Good practice to use reverse indexes on surrogate keys? (Oracle)

问题描述

假设我有一个带有自动递增代理键的表。

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

使用反向索引这是一个好例子吗？

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

我说的是正确的：

插入（进入索引）会更快..因为新值会随机插入，而不是总是转到最右边的叶子（不断强制重新平衡）。

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).

（注意，我没有使用Oracle RAC）

(Note, I'm not using Oracle RAC)

推荐答案

一般情况下，如果你不使用RAC，那就没有了使用反向键索引的原因。

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

从性能的角度来看，在任何给定的时间点都有一个或两个热块会更好插入，因为这基本上保证了热块将在缓冲区缓存中，并且 INSERT 不必承担从磁盘读取块的成本。如果你有一个插入进入索引中的随机块，那么你想要的块有可能老化掉缓存，并且会产生物理I / O的成本。

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.

保持指数平衡的成本非常小，但即便有利于标准指数。如果你有一个序列生成的主键具有正常索引，Oracle将执行 90/10块分割。相反，如果你有一个反向键索引，Oracle必须做 50/50块分割。 50/50块拆分将旧块的一半数据复制到新块，90/10块拆分仅将最右边的数据值复制到新块。因此，90/10块分割比50/50块分割便宜得多，无论您选择哪种索引，都需要进行大致相同数量的块分割。因此，维护常规索引的成本低于维护反向键索引的成本，甚至忽略缓存的影响。

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.

您考虑使用反向的原因密钥索引将是您正在使用RAC，并且您希望避免让许多RAC节点都在同一个热块上进行争用的成本。如果您经常需要将热块从一个节点发送到另一个节点以进行下一次插入，则可能值得使用反向键索引来减少该争用。如果您已获得分区选项的许可，则最好还是使用散列分区索引（无论表是否已分区，都可以这样做）。如果您尚未许可分区选项，则反向键索引可能足以解决热块上的争用，从而不需要您对许可证进行分区。

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.

这篇关于在代理键上使用反向索引的好习惯？ （Oracle）的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！