Oracle中组合索引建立的顺序疑问

[applewings2014]

我今天看有关SQL优化的内容，其中提到建立组合索引最好是将 经常等值查询的列放在前面
例如 表中有 name, time 两个字段，name经常被等值查询，time经常被范围查询
那么如果需要将这两个字段建组合索引的时候，就应该建成（name, time）这样的顺序

给出的解释是：因为索引都是有序的，组合索引根据前导列排序，也就是name排序；如果查询name=abc，当Oracle看到name=b~~的就可以认为后面再无abc了，就不继续往下找了
例如查询 name=abc and time>2007 and time < 2014
Oracle会从name往下扫描，当扫描到aca就会停止扫描，即便name=abc的记录对应的time只有2008，2009其他年份都没有，Oracle也不会继续往后找了

但是如果建立成（time， name）索引中按照time排序，那么Oracle 就会遍历time = 2008,2009,2010,2011,2012,2013
即便2010~2013都没有name=abc的情况，Oracle也会都遍历到

所以建立组合索引要把经常等值查询的放前面

但是对于这个解释我就有疑问了，我觉得不一定吧，我考虑了一个极端情况
表中的name全是abc，那如果再建立（name， time）这样的索引，不就出现不了他说的到aca自动停止的情况了？这不就相当于遍历全部索引了？
如果建成（time，name）这不就反倒对扫描范围有所限定了？

当然了，我说的这个极端情况加name=abc根本是没意义的，只是就事论事，不考虑与这个问题无关的事

但是我自己做了个试验，我往一个表中插了50万数据，我把所有的name全设定成abc，结果却出乎我意料了，依旧是（name，time）性能更好，而不是我想的那种情况，请问这是为什么？还是我对组合索引的查询原理理解有误？ 请指教

[Gospelanswer]

不知道你用的是什么版本的库，你对组合索引的理解没有错。但是一般情况下，貌似oracle会根据自己CBO的算法来进行解析一个sql，然后找到最优的方式去执行，就好比有的时候建索引并不是最好的优化方式。

[applewings2014]

引用 1 楼 Gospelanswer 的回复:

不知道你用的是什么版本的库，你对组合索引的理解没有错。但是一般情况下，貌似oracle会根据自己CBO的算法来进行解析一个sql，然后找到最优的方式去执行，就好比有的时候建索引并不是最好的优化方式。

您的意思是说，在我说的那种情况下，理论上（name，time）索引是没有（time，name）高的，但是Oracle根据情况进行了优化，所以体现出来的还是（name，time）更好么？ 我用的11g

[applewings2014]

引用 3 楼 wildwave 的回复:

你的语句是怎么写的
还有两个不同顺序的组合索引下的执行计划是什么样的

建表和查询语句，以及收集统计信息如下：


可以看得出第二个语句的buffers还是小于第一个语句，我把object_type全改成了a，按理说执行（type, id ）应该遍历的比（id， type）的范围更广吧

我理解的，感觉应该是第二个语句buffers更大

[xinpingf]

说一句，就一句： 如果你的一列中的数据全是abc，那你索引他干毛线用？

[liao_z_j]

它是当你有前导列时，条件中只有前导列这个条件时也会用到组合索引，不用两个列作为条件。 比如 (A,B)建了组合索引 当WHERE A='sf'时会用到索引的 而WHERE B='sd'是不会用到组合索引的。

[applewings2014]

感谢各位回答了
我自己又仔细分析了一下，在excel中一行一行手工分析了一下，其实两种查询应该是一样的，因为Oracle的组合索引是，优先按照前面的列排序，前面的列一致的情况下按照下一列排序

查询时，第一组合列首先充当于一个类似刹车器的功能，扫描到超过where条件，就停止查询，这样也是我的第一条语句（范围在前）
而在第一组合列一致的情况下，第二组合列就开始充当刹车器，所以当我执行我的第二条语句（等值在前）时，虽然object_type已经被我全设成a，没有过滤功能了，但是在type一致时，第二列的object_id启动排序充当刹车器，所以还是扫描到2000自动停止了

不会像我最一开始说的一直扫描下去，所以我说的这种极端情况，应该是无论谁在前谁在后，效果是一样的，区别无非是被第一组合条件限定或是第二组合条件限定的问题

我后来重新建表，重建弄了一次，结果真就一样了，丝毫不差


估计是因为我昨天在这个表上折腾太多别的操作了，索引又没重建，不定什么地方乱了


因为想到了后面的列也能充当刹车器的事，我后来又试验了下
建立了个三列索引（object_type, object_id, owner）,然后把object_type全给a，object_id给成1,2,3这三个值
当我查object_type=a and object_id >=1 and object_id <=2 果然比object_type=a and object_id >=1 and object_id <= 3快了近一倍
证实了我的猜想，老大没了，老二顶了上去在2最后的位置产生了刹车效果

[Gospelanswer]

我今天试了一下，感觉你应该没有重新收集统计信息。如果重新收集统计信息的话，应该都走table access full。不会走索引。我用的是80万的数据量

[小灰狼W]

你的语句是怎么写的 还有两个不同顺序的组合索引下的执行计划是什么样的

[专注or全面]

我觉得组合索引的选择看所在列的范围吧， 比如ID，TYPE查询，ID是范围，TYPE是等值 如果1千万条数据每个TYPE都不一样，ID在一个较小的范围内，把索引建成ID，type顺序的，肯定没有TYPE，ID的顺序效率高

[小灰狼W]

如果不加hint，这条语句最优的应该是走object_id列的单列索引，这是由于object_type并不起过滤作用，此时object_id单列索引占用的空间更小，逻辑读更少
当走组合索引的时候，即使object_type选择性不高，也倾向于走(object_type,object_id)组合索引。该组合索引先按object_type排序，再按object_id排序，也就是说，先通过object_type的等值查询找到object_type='a'的部分，再对第二列进行范围查询。这种方式产生的io要比先从object_id中找出一堆的记录再一个一个判断后面的object_type要低。因此走object_type前导的组合索引Buffer要更小一些
这种逻辑读的差距，当object_id过滤的结果集越小时，差别就越小。当你下面的那个查询，object_id限定到1条的时候，逻辑读是一样的

[x1524378424]

。。。不明白

[xiaoxiangqing]

建索引本来就是重复值不能太多，如果太多，就没有建索引的意义

[zdq56]

返回值太多了不利于建索引，尤其是组合索引，你那种极端的说法本来不应该那样处理的