不胜人生一场醉

两个好像都是直接读取索引块,而无需读取数据块的情况.前提是待查询字段已经包含在该组合索引中.
不过看Oracle9i Database Performance Tuning Guide and Reference,好像没太多区别
Full Scans

A full scan is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. A full scan is also available when there is no predicate, if both the following conditions are met:

n All of the columns in the table referenced in the query are included in the index.

n At least one of the index columns is not null.

A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. It reads the blocks singly.

翻译:当一个断言使用索引列中的一个时,就会使用到全扫描.断言不需要是一个索引驱动.没有断言时,也可能会使用全扫描.但是要满足以下两个条件:

查询引用的所有列必须包含在索引中

至少一个索引字段不能为空

全扫描被用来消除排序操作,因为索引键中的数据已经预先排列好,它将会逐一读取数据块.

Fast Full Index Scans

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized. Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint.

Fast full index scans cannot be performed against bitmap indexes. A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

快速全索引扫描时全扫描的替代,当索引包含查询所需要的全部列的时候.并且至少索引键中的一列不能为空,Fast Full Index Scans仅仅访问索引中的数据.它不能消除排序操作,索引键中的数据时未排序的. Fast Full Index Scans通过多块读取方式读取全部索引,(和全扫描不同),也能构并行化. Fast Full Index Scans只在CBO模式下有效,可以通过设置OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS的方式强制使用Fast full index scans,全扫描不能用在位图索引上, Fast full index scans要比全扫描快一些,因为它能够多块读取,并且像全表扫描一样并行化.

index full scan需要先从root定位到第一个leaf block，然后按顺序一个一个读取所有的leaf block，所以index full scan可以用来避免某些sort操作，这个full scan的名字有点误导人，其实并不是所有的index block都被读取的，某些分支块是不会读到的

index fast full scan则读取index的所有block，包括branch block，并且是multiblock的读取方式，所以index fast full scan不能用来消除sort

index full scan是避免排序，因为索引已经排序，不是读取索引的全部块。通过链接读取下一块。

index fast full scanFast Full Index Scans，全部读取，包括根，叶等结点。充分发挥多块读的特性。

摘自 http://www.dbanotes.net/Oracle/Index_full_scan_vs_index_fast_full_scan.htm

当进行index full scan的时候 oracle定位到索引的root block，然后到branch block（如果有的话），再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。

而index fast full scan则不同，它是从段头开始，读取包含位图块，root block,所有的branch block, leaf block，读取的顺序完全有物理存储位置决定，并采取多块读，没次读取db_file_multiblock_read_count个块。

Example: