SQL优化是一个比较复杂的事情，想要做好优化一要靠扎实的技术功底，二要靠丰富的实践经验，这里来谈一下SQL优化中的sort优化问题。
在之前的优化经验中，错误的认为只要结果集不大，含有order by的SQL可以不用考虑sort的消耗。在最近的一次测试中，在梁神的指导下，才发现原来单独启动sort需要很大的开销。下面介绍下如何去掉sort。
1、 单表查询消sort
对于单表查询含有order by 的SQL，这个消掉sort比较简单，建个对应的索引即可，如：
建表：

CREATE TABLE TAB02
(
ID CHAR(10),
NAME CHAR(10),
ADDR CHAR(10));

插入测试数据：

insert into tab02 select level,'a'||level,sysdate-level from dual connect by level <=100;
commit;
select * from tab02 a where id > 98 order by id desc

执行计划：

1   #NSET2: [1, 5, 112] 
2     #PRJT2: [1, 5, 112]; exp_num(4), is_atom(FALSE) 
3       #SORT3: [1, 5, 112]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #SLCT2: [1, 5, 112]; A.ID > 98
5           #CSCN2: [1, 112, 112]; INDEX33555464(TAB02 as A)

创建索引并收集统计信息：

create index IDX_TAB02_ID on tab02(id desc);
sp_Index_stat_Init('SYSDBA','IDX_TAB02_ID');

执行计划：

1   #NSET2: [1, 2, 112] 
2     #PRJT2: [1, 2, 112]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [1, 2, 112]; IDX_TAB02_ID(A)
4         #SSEK2: [1, 2, 112]; scan_type(ASC), IDX_TAB02_ID(TAB02 as A), scan_range(null2,98)

我们可以看到sort已经去掉了。
再插入100条数据：

insert into tab02 select level,'a'||level,sysdate-level from dual connect by level <=100;
commit;
select * from tab02 a where id = 99 order by addr desc

查看执行计划：

1   #NSET2: [1, 2, 112] 
2     #PRJT2: [1, 2, 112]; exp_num(4), is_atom(FALSE) 
3       #SORT3: [1, 2, 112]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #BLKUP2: [1, 2, 112]; IDX_TAB02_ID_ADDR(A)
5           #SSEK2: [1, 2, 112]; scan_type(ASC), IDX_TAB02_ID_ADDR(TAB02 as A), scan_range[99,99]

发现sort又出来了，这个时候怎么去掉sort呢？我们可以建个组合索引：

drop index IDX_TAB02_ID_ADDR;
create index IDX_TAB02_ID_ADDR on tab02(id, addr desc );
sp_Index_stat_Init('SYSDBA','IDX_TAB02_ID_ADDR');

再看看执行计划：

1   #NSET2: [1, 2, 112] 
2     #PRJT2: [1, 2, 112]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [1, 2, 112]; IDX_TAB02_ID_ADDR(A)
4         #SSEK2: [1, 2, 112]; scan_type(ASC), IDX_TAB02_ID_ADDR(TAB02 as A), scan_range[(99,min),(99,max))

可以看到sort也没有了。

2、 二表关联查询消sort
对与单表查询的SQL去掉sort相对简单点，对于二表关联的SQL，去掉sort还是比较麻烦的。
建表：

create  table t1 (organ_no varchar2(20),parent_organ varchar2(20),organ_level varchar2(20),crt_time datetime,mod_time datetime,trans_Id varchar2(20));
create  table t2 (trans_Id varchar2(20),organ_no varchar2(20),parent_organ varchar2(20),organ_level varchar2(20),crt_time datetime,mod_time datetime,
	VOUCH_GROUP varchar2(10),IS_OPEN varchar2(5),up_flag varchar2(5));

插入测试数据：

insert into t1 select 1000000+level,   1000000+level-1,   mod(level,5) ,sysdate-level,sysdate-level,200000+level from dual connect by level<=100;
commit;
insert into t1 select 1000000+level,   1000000+level-1,   mod(level,5) ,sysdate-level,sysdate-level,200000+level from dual connect by level<=100;
commit;
insert into t2 select 200000+level,1000000+level,   1000000+level-1,   mod(level,5) ,sysdate-level,sysdate-level,999000+level,mod(level,3),mod(level,6) from dual connect by level<=100;
commit;

update t2 set t2.organ_no=t2.organ_No-1;

建索引并收集统计信息：

create OR REPLACE index IDX_T1_ORGAN_NO on t1(ORGAN_NO);
create OR REPLACE index IDX_T2_TRANS_ID on t2(TRANS_ID,ORGAN_NO);
sp_index_stat_init('SYSDBA','IDX_T1_ORGAN_NO');
sp_index_stat_init('SYSDBA','IDX_T2_TRANS_ID');

查看SQL及执行计划：

SELECT
        t2.ORGAN_NO
FROM
        t1 ,
        t2 
WHERE
        t1.ORGAN_NO     = '1000038'
    AND t2.TRANS_ID     = '200038'
    AND t2.VOUCH_GROUP  = '999038'
    AND t2.IS_OPEN      >'0'
    AND t2.UP_FLAG      <'3'
    AND t1.PARENT_ORGAN = t2.ORGAN_NO
ORDER BY
        t1.ORGAN_LEVEL DESC

1   #NSET2: [1, 5, 384] 
2     #PRJT2: [1, 5, 384]; exp_num(1), is_atom(FALSE) 
3       #SORT3: [1, 5, 384]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #SLCT2: [1, 5, 384]; (T2.VOUCH_GROUP = '999038' AND T2.IS_OPEN > '0' AND T2.UP_FLAG < '3')
5           #NEST LOOP INDEX JOIN2: [1, 5, 384] 
6             #BLKUP2: [1, 5, 144]; IDX_T1_ORGAN_NO(T1)
7               #SSEK2: [1, 5, 144]; scan_type(ASC), IDX_T1_ORGAN_NO(T1), scan_range['1000038','1000038']
8             #BLKUP2: [1, 1, 96]; IDX_T2_TRANS_ID(T2)
9               #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_T2_TRANS_ID(T2), scan_range[('200038',T1.PARENT_ORGAN),('200038',T1.PARENT_ORGAN)]

一般情况下，我们认为这个结果很好了，可以不用优化了，但是在某些情况下，这个sort去掉的话，对整个性能提升还是有很大帮助的，下面就当前这种场景去掉sort。

create OR REPLACE index IDX_T1_ORGAN_NO on t1(ORGAN_NO,ORGAN_LEVEL DESC,PARENT_ORGAN);
create OR REPLACE index IDX_T2_TRANS_ID on t2(TRANS_ID,ORGAN_NO);
sp_index_stat_init('SYSDBA','IDX_T1_ORGAN_NO');
sp_index_stat_init('SYSDBA','IDX_T2_TRANS_ID');

再看看执行计划：

1   #NSET2: [1, 5, 384] 
2     #PRJT2: [1, 5, 384]; exp_num(1), is_atom(FALSE) 
3       #SLCT2: [1, 5, 384]; (T2.VOUCH_GROUP = '999038' AND T2.IS_OPEN > '0' AND T2.UP_FLAG < '3')
4         #NEST LOOP INDEX JOIN2: [1, 5, 384] 
5           #SSEK2: [1, 5, 144]; scan_type(ASC), IDX_T1_ORGAN_NO(T1), scan_range[('1000038',min,min),('1000038',max,max))
6           #BLKUP2: [1, 1, 96]; IDX_T2_TRANS_ID(T2)
7             #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_T2_TRANS_ID(T2), scan_range[('200038',T1.PARENT_ORGAN),('200038',T1.PARENT_ORGAN)]

我们可以看到sort已经没有了。
这里面需要注意，当t2的结果集只有1条，t1的结果集有2条的时候，默认的顺序是先做t2再做t1，这样的话sort也是没法消除的，可以通过增加hint改变t1，t2的顺序：

SELECT/*+order(t1 t2)*/
        t2.ORGAN_NO
FROM
        t1 ,
        t2 
WHERE
        t1.ORGAN_NO     = '1000038'
    AND t2.TRANS_ID     = '200038'
    AND t2.VOUCH_GROUP  = '999038'
    AND t2.IS_OPEN      >'0'
    AND t2.UP_FLAG      <'3'
    AND t1.PARENT_ORGAN = t2.ORGAN_NO
ORDER BY
        t1.ORGAN_LEVEL DESC

这里要注意一点，索引列的顺序，本着先过滤再排序再关联的顺序，不然是没法消除sort的。
3、 TOP + ORDER且ORDER BY列属于索引前导列
这种是文档上面的，参考文档上面的例子，建表并构造数据：

DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1(C1 INT, C2 INT);
CREATE TABLE T2(D1 INT, D2 INT);
INSERT INTO T1 SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 80000;
INSERT INTO T2 SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 80000;
COMMIT;
CREATE INDEX IND1 ON T1(C1, C2);
CREATE INDEX IND2 ON T2(D1);
SELECT /*+ TOP_ORDER_OPT_FLAG(0) */ TOP 10 C1, D1, D2 FROM T1 JOIN T2 ON C1 = D1 ORDER BY C1;

执行计划：

1   #NSET2: [4151, 10, 12] 
2     #PRJT2: [4151, 10, 12]; exp_num(3), is_atom(FALSE) 
3       #SORT3: [4151, 10, 12]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4         #HASH2 INNER JOIN: [33, 63296639, 12];  KEY_NUM(1); KEY(T1.C1=T2.D1) KEY_NULL_EQU(0)
5           #SSCN: [8, 80000, 4]; IND1(T1)
6           #CSCN2: [8, 80000, 8]; INDEX33555496(T2)

执行结果：
SELECT /*+ TOP_ORDER_OPT_FLAG(0) */ TOP 10 C1, D1, D2 FROM T1 JOIN T2 ON C1 = D1 ORDER BY C1;
执行成功, 执行耗时9毫秒. 执行号:2198
通过et看，耗时主要在sort上，如图：

调整hint：

SELECT /*+ TOP_ORDER_OPT_FLAG(1) */ TOP 10 C1, D1, D2 FROM T1 JOIN T2 ON C1 = D1 ORDER BY C1;
执行计划：
1   #NSET2: [2, 10, 12] 
2     #PRJT2: [2, 10, 12]; exp_num(3), is_atom(FALSE) 
3       #TOPN2: [2, 10, 12]; top_num(10)
4         #NEST LOOP INDEX JOIN2: [2, 600, 12] 
5           #SSCN: [1, 300, 4]; IND1(T1)
6           #BLKUP2: [1, 2, 4]; IND2(T2)
7             #SSEK2: [1, 2, 4]; scan_type(ASC), IND2(T2), scan_range[T1.C1,T1.C1]

执行结果：
SELECT /*+ TOP_ORDER_OPT_FLAG(1) */ TOP 10 C1, D1, D2 FROM T1 JOIN T2 ON C1 = D1 ORDER BY C1;
执行成功, 执行耗时1毫秒. 执行号:2217