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.

=================================

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 ;

ffs只能在cbo下使用，我想主要原因是並行的關係 .

=================================

1. 是避免排序，因為索引已經排序，不是讀取索引的全部塊。透過連結讀取下一塊。

2。Fast Full Index Scans，全部讀取，包括根，葉等結點。
充分發揮多塊讀的特性。

Index Full Scans和Index Fast Full Scans

相關文章