Reverse Key Indexes (219)

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.

反轉索引
1. 將索引列值的每個位元組進行倒序排列(除了rowid) ，在RAC環境中可以避免由於對索引的修改集中在一小部分葉塊
上的效能下降 ，透過使索引的鍵值逆序排列，可以使插入操作分佈在索引的全部葉塊中。
2. 使用反轉索引後將無法對此索引進行索引區間掃描
3. 有些情況下，使用逆序鍵索引可以使 RAC 環境下的 OLTP 應用效率更高
4. 在建立索引時加上關鍵字 REVERSE 即建立一個反轉索引
使用ALTER INDEX REBUILD NOREVERSE可將反轉索引重建為非反轉索引

[@more@]