ITPub博客

首页 > 数据库 > 数据库开发技术 > Reverse Key Indexes (219)

Reverse Key Indexes (219)

原创 数据库开发技术 作者:tsinglee 时间:2007-11-19 11:17:56 0 删除 编辑

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@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10599713/viewspace-982963/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Bitmap Indexes (220)
请登录后发表评论 登录
全部评论
  • 博文量
    740
  • 访问量
    1892393