reverse index的困惑
时间:2010-11-01 来源:lovegigi1999
reverse index
例子:create index idx_reverse on test_reverse(x) reverse;
形式:讲b树索引反转存放,例如1234存为4321
目的:为了可以分散key,避免热点块,尤其是并行系统热点块需要pinging到硬盘的情况
用途:主要用于序列增加的列值
参考:tom expert on to on
问题:使用反转索引的情况就是为了避免这种序列的列可以分散存放,对于序列式的列最常用的应该就是where x>5或者<5这样的句子,可是反转偏偏对这种查询不起作用,那我什么时候需要反转索引?
例子:create index idx_reverse on test_reverse(x) reverse;
形式:讲b树索引反转存放,例如1234存为4321
目的:为了可以分散key,避免热点块,尤其是并行系统热点块需要pinging到硬盘的情况
用途:主要用于序列增加的列值
参考:tom expert on to on
The numbers will end up 'far away' from each other. This reduces the number of instances going after the same block (the leftmost block) and reduces the amount of pinging going on. One of the drawbacks to a reverse key index is that you cannot utilize it in all of the cases where a regular index can be applied. For example, in answering the following predicate, a reverse key index on x would not be useful:
where x > 5
The data in the index is not sorted before it is stored, hence the range scan will not work.
问题:使用反转索引的情况就是为了避免这种序列的列可以分散存放,对于序列式的列最常用的应该就是where x>5或者<5这样的句子,可是反转偏偏对这种查询不起作用,那我什么时候需要反转索引?
相关阅读 更多 +