关于组合索引的排序效率
转自 http://www.dbaxiaoyu.com/archives/1872
Itpub上的一篇帖子http://www.itpub.net/thread-1851403-3-1.html ,其中是提到sql语句排序与不排序执行效率为何变化如此之大。
版本oracle 9I ：
select *
from a1 t
where t.j_uptime >=to_date('2014-02-01', 'yyyy-mm-dd')
and t.j_uptime <=to_date('2014-03-01', 'yyyy-mm-dd')
order by t.j_date desc
业务的需求是应该排序的，但是排序后执行时间太长了，我们来分析下为何会造成如此大的差异
相应的执行计划如上图，这里发现主要是执行计划出现了较大的变化，没加排序时是index range scan然后回表，而加排序后则是index full scan descending然后回表，而index full scan descending和index full scan的扫描方式大体是一样的。
index full scan descending是先通过root节点访问到必要的分支块节点（分支块节点的最左边和右边都分别保存了一个指向下层最左边和右边的块的指针），通过分支块的最右边的叶块节点根据索引间的双向指针，从右到左倒序扫描所有的叶块节点，注意的是index full scan descending是需要扫描所有的叶块节点，但是并不需要扫描所有的分支块节点，由于索引扫描是有序的，所以通过index full scan descending扫描的数据也是有序的，这样可以避免最外层的排序操作。
Index range scan也是通过root节点访问到必要的分支块节点，然后扫描相应的叶块节点。
没加order by时，毫无疑问inde range scan是最合适的，此时并不需要扫描所有索引的叶块，只需要扫描满足where条件的索引的叶块节点然后回表即可。
加order by对索引的另一列jscores_date排序时，此时index range scan返回的数据在cbo看来是无需的，因为排序时没有加索引的前导列jscores_uptime，回表后还需要sort by，cbo考虑成本计算会直接选择了成本较低的index full scan和通过rowid回表的执行计划，而这个会扫描所有的索引建值然后回表。
感觉这里cbo的选择可能并不合适，索引是单块读，而且由于回表的数据物理块不连续，会带来较多的随机读和重复读，cbo可能过多的估算了这个排序操作占据cost的比例，而且随着数据量的增加，索引键值的增多，cbo这种index full scan然后回表来避免排序的方式，IO消耗会越来越大。9I下并没有自动收集统计信息的job，可以试着收集下最新统计信息看看，cbo是否会改变成先index range scan然后回表，最后sort排序！
如果我们加上索引前导列，oracle会如何选择了，下面来分别看下：
SQL> create table table01 as select * from dba_objects;
SQL> create index ind_table_mulit on table01(object_id,created);
索引已创建。
SQL> select * from table01 where object_id<10000 order by created;
已选择9707行。
执行计划
----------------------------------------------------------
Plan hash value: 69786109
--------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 9758 | 952K| |
393 (1)| 00:00:05 |
| 1 | SORT ORDER BY | | 9758 | 952K| 1312K|
393 (1)| 00:00:05 |
| 2 | TABLE ACCESS BY INDEX ROWID| TABLE01 | 9758 | 952K| |
170 (1)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | INDEX_TABLE_ID | 9758 | | |
23 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<10000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
475525 bytes sent via SQL*Net to client
7640 bytes received via SQL*Net from client
649 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9707 rows processed
SQL> select * from table01 where object_id<10000 order by object_id,created;
已选择9707行。
执行计划
----------------------------------------------------------
Plan hash value: 400278357
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 9758 | 952K| 181
(1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE01 | 9758 | 952K| 181
(1)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | IND_TABLE_MULIT | 9758 | | 34
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<10000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1453 consistent gets
28 physical reads
0 redo size
1020151 bytes sent via SQL*Net to client
7640 bytes received via SQL*Net from client
649 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9707 rows processed
所以这里还有一个非常靠谱的办法就是在允许情况下修改业务逻辑，把索引的前导列写上，此时cbo会选择index range scan回表的方式，由于加上索引前导列后，index range scan的数据就已经是有序的，这里可以有效的用索引避免排序。
