ORDER BY是否走索引情况分析

1.ORDER BY 索引列
走索引
示例:
12:15:49 SCOTT@orcl> select * from emp order by empno;
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 4004826672
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 560 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 560 | 2 (0)| 00:00:01 || 2 | INDEX FULL SCAN | EMP_PK | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
显然走了主键索引EMP_PK.
2.order by 索引列a,索引列b。
不走索引
示例:
--查看索引
12:18:48 SCOTT@orcl> select INDEX_NAME,COLUMN_NAME from user_ind_columns where table_name='EMP';
INDEX_NAME COLUMN_NAME
---------------------------------------------------------------------------------
EMP_PK EMPNO
TEST_INX ENAME
已选择2行。
--
12:16:50 SCOTT@orcl> select * from emp order by empno,ename;
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 150391907
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 560 | 719 (1)| 00:00:09 || 1 | SORT ORDER BY | | 14 | 560 | 719 (1)| 00:00:09 || 2 | TABLE ACCESS FULL| EMP | 14 | 560 | 718 (1)| 00:00:09
|---------------------------------------------------------------------------
3.order by 复合索引.
走复合索引(注意ORDER BY的顺序与复合索引要一致)
新增复合索引.
12:23:37 SCOTT@orcl> select INDEX_NAME,COLUMN_NAME from user_ind_columns where table_name='EMP';
INDEX_NAME COLUMN_NAME
------------------------------ ---------------
EMP_PK EMPNO
TEST_INX ENAME
CMD_IND ENAME
CMD_IND EMPNO
已选择4行。
已用时间: 00: 00: 00.01
--查看执行计划
12:10:24 SCOTT@orcl> select * from emp order by empno,ename;
已选择14行。
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1060919621
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 560 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 560 | 2 (0)| 00:00:01 || 2 | INDEX FULL SCAN | COM_IND | 14 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------I