[Index]组合索引(Composite Index)中多个字段的顺序

一个表中有三个字段,XX,YY,ZZ,如果要建立给这三个字段建组合索引(Composite Index),组合索引中字段的顺序应该遵循怎样一个原则。

一般的原则:越离散的字段越靠前。哪个列可以降低索引的扫描成本就放在前面。

比如:下位三个字段的离散情况
XX:2
YY:1000
ZZ:50000

那么建立索引的顺序应该为:ZZ,YY,XX

CREATE INDEX t_idx ON t (zz,yy,xx);


但是如果where条件中,三个字段的条件都是通过"="号连接的,那么组合索引中字段的顺序就是无所谓了。

Refer:https://forums.oracle.com/forums/thread.jspa?threadID=2425684

Because you are using equality conditions in your predicate, then the order should be XX, YY, ZZ for maximum compression. Others are suggesting ZZ should be first because it is more selective (probably). But if you are using equals (=) for all 3, then that really does not matter.


或者It’s Less Efficient To Have Low Cardinality Leading Columns In An Index (Right) ? (轻功需好)

In actual fact, there’s no real difference in navigating to the specific leaf block of interest for an index on (ID, CODE) compared to an index based on (CODE, ID), providing both indexed columns are known.


再或者 http://www.oraclemagician.com/white_papers/index_order.pdf

Consider the following indexes:
INDEX1: (ZIP_CODE, GENDER)
INDEX2: (GENDER, ZIP_CODE)
Assume we are looking for the combination of Zip_Code = 94568 and Gender = ‘Male.’ Before Oracle traverses the index, it combines the two conditions. The composite value, something like 94568_Male has the same discriminatory value as Male_94568.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值