The Oracle (tm) Users' Co-Operative FAQ
What is the difference between an 'index full scan' and an 'index fast full scan' ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 26th July 2001 Oracle version(s): 7.3 - 8.1.7.0 |
| When using Explain Plan, one of the possible operations on an index is the full scan, but there is another that appears occasionally that is called the fast full scan. What is the difference between these two operations ? |
The fast full scan was an operation that appeared in Oracle 7.3, although in that version of Oracle there was a time when it had to be invoked explicitly by the hing /*+ index_ffs(alias index) */.
Under this operation, the index would not be treated like an index, it would be treated as if it were a narrow table, holding just the columns defined as part of the index. Because of this, Oracle is able to read the index segment using multiblock reads (discarding branch blocks as it goes), even using parallel query methods to read the segment in the shortest possible time. Of course, the data coming out of an index fast full scan will not be sorted.
The full scan, however, simply means that Oracle walks the index from end to end, following leaf blocks in the right order. This can be quite slow, as Oracle can only use single block reads to do this (although in fact 8.1.5ish introduced the _non_contiguous_multiblock_read, and various readahead strategies). However the results do come out in index order without the need for an sort. You might notice that Oracle tends to choose this option when there is an index that happens to suit the order by clause of a query, even if it doesn't suit the where clause of the query - this may also result in a SORT ORDER BY (NO SORT) line appearing in your execution plan.
Further reading: N/A
