为什么INDEX FAST FULL SCAN需要排序？
创建索引后由于需要查询的字段都可以通过索引获取，所以不再需要访问具体的数据表，数据库通过INDEXFAST FULL SCAN来完成查询，由于查询包含一个ORDER BY排序运算，执行计划中显示了SORTORDER BY的过程，统计数据中显示了查询产生了一次内存排序：
SQL> select eeode,cname,shortcode from cnderp.eeode whereeeode>' ' order by eeode
2 /
------------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes |TempSpc |Cost (%CPU) |
------------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 66567 | 2470K | | 761 (2) |
| 1 | SORTORDER BY | | 66567 | 2470K | 6280K | 761 (2) |
| 2 | INDEX FAST FULL SCAN | EEODE_EEODE_CNAME | 66567 | 2470K | | 100 (1) |
------------------------------------------------------------------------------------------
446 consistent gets
1 sorts (memory)
0 sorts (disk)
66566 rows processed
大家都知道索引是有序的数据存储，那么为什么INDEX FAST FULL SCAN需要排序呢？INDEXFAST FULL SCAN与常见的INDEX FULL SCAN又有何区别呢？
图13-1是最常用的B*Tree索引示意图：
图13-1 最常用的B*Tree索引示意图
基于索引的有序存储，如果读取时从叶节点自左向右读取，读取出来的数据就将是有序的，如果读取扫描一定的范围就是通常所说的INDEXRANGE SCAN；如果顺序读取所有的叶节点这就是INDEX FULL SCAN。
由于顺序读取的数据是有序的，INDEX FULL SCAN可以消除某些查询的排序操作；而INDEXFAST FULL SCAN是指通过多块读读取全部索引的数据块，数据读取时，是按照块的连续性，以Extent为单位来读取的（如同进行全表扫描一样），所以获取的数据是无序的。
这就是几种常见索引访问的区别。
通过测试来具体看一下几种索引访问方式的异同，以下测试来自Oracle10gR2版本。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise EditionRelease 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 -Production
NLSRTL Version 10.2.0.3.0 – Production
首先基于DBA_OBJECTS创建一个测试表，并创建索引：
SQL> create table eygle as select *from dba_objects where 1=0;
Table created.
SQL> create index idx_object_id oneygle(object_id);
Index created.
SQL> insert into eygle select *from dba_objects where rownum <10001;
10000 rows created.
SQL> commit;
Commit complete.
SQL> alter table eygle modify(object_id not null);
Table altered.
分析数据，收集统计信息：
SQL> analyze table eygle compute statistics for table for all indexes forall indexed columns;
Table analyzed
做一个10条记录输出的间查询，默认的数据库选择了INDEX FASTFULL SCAN，使用了7个逻辑读：
SQL> set autotrace on
SQL> select object_id from eyglewhere rownum <11;
OBJECT_ID
----------
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 372913538
----------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 10 | 40 | 2 (0) | 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FAST FULL SCAN | IDX_OBJECT_ID | 10 | 40 | 2 (0) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
10 rows processed
如果手工指定索引，此时数据库选择了INDEX FULL SCAN，使用了3个逻辑读，输出的结果自低至高，是从索引的最左端开始读取的：
SQL> select /*+ index(eygleidx_object_id) */ object_id from eygle where rownum <11;
OBJECT_ID
----------
2
3
4
5
6
7
8
9
10
11
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3614676306
-----------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU) |Time |
-----------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 10 | 40 | 2 (0) | 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEXFULL SCAN | IDX_OBJECT_ID | 10 | 40 | 2 (0) | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1 - filter(ROWNUM<11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistentgets
0 physical reads
10 rows processed
如果增加ORDER BY子句，INDEX FAST FULL SCAN则须要排序：
SQL> select/*+ index_ffs(eygle idx_object_id) */ object_id
2from eygle where rownum <11 order by object_id;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1286843348
---------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes |TempSpc |Cost (%CPU) |
---------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 10 | 40 | | 40 (8) |
| 1 | SORTORDER BY | | 10 | 40 | 248K | 40 (8) |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | INDEX FAST FULL SCAN | IDX_OBJECT_ID | 10000 | 40000 | | 6 (0) |
---------------------------------------------------------------------------------------
而INDEX FULL SCAN 则无须排序：
SQL> select object_id from eyglewhere rownum <11 order by object_id;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3614676306
-----------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 10 | 40 | 2 (0) | 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEXFULL SCAN | IDX_OBJECT_ID | 10000 |40000 | 2 (0) | 00:00:01 |
-----------------------------------------------------------------------------------
文章来源：《Oracle DBA手记1》第13章 索引访问与数据读取 作者：盖国强
配图来源：http://cn.dreamstime.com/
回复“手记1”可下载本书前两篇内容：DBA工作手记/诊断案例篇(建议复制链接至电脑端）