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.