为什么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工作手记/诊断案例篇(建议复制链接至电脑端）