Reverse Key Indexes

Reverse Key Indexes

反向键值索引

 

表acc情况如下

SQL> select * from acc;

 

       ID

----------

      123

      124

      125

 

如对acc的id字段创建索引，因为值是连续的所以连续值在索引中在同一个块中的可能性比较大，如果数据频繁更新可能会出现比较集中的争用。

创建反向索引，索引创建的时候所使用的值就变成了321、421、521.这样连续数据出现在同一个块中的几率会减小，争用的可能也就降低。

另外反向索引对前模糊查询，也就是like ‘%aa’正中情况的查询同样有效，所以需要时使用还是很方便的。

 

缺点是对于index range scan无效，也就是说创建了反向索引后where id >121这种情况是用不到索引的。

 

以下是官方文档中的解释
Creating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation with Real Application Clusters where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.

Using the reverse key arrangement eliminates the ability to run an index range scanning query on the index. Because lexically adjacent keys are not stored next to each other in a reverse-key index, only fetch-by-key or full-index (table) scans can be performed.

Sometimes, using a reverse-key index can make an OLTP Real Application Clusters application faster. For example, keeping the index of mail messages in an e-mail application: some users keep old messages, and the index must maintain pointers to these as well as to the most recent.

The REVERSE keyword provides a simple mechanism for creating a reverse key index. You can specify the keyword REVERSE along with the optional index specifications in a CREATE INDEX statement:

CREATE INDEX i ON t (a,b,c) REVERSE;


You can specify the keyword NOREVERSE to REBUILD a reverse-key index into one that is not reverse keyed:

ALTER INDEX i REBUILD NOREVERSE;


Rebuilding a reverse-key index without the NOREVERSE keyword produces a rebuilt, reverse-key index