What is the difference between Fast Index Scan and Fast Full Index Scan ?
As per oracle documentation,
” 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:
?All of the columns in the table referenced in the query are included in the index.
?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 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.
You can specify fast full index scans 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 “.
I am not clear about this.Could you please clarify.