Difference Between Full Index Scans and Fast Full Index Scans

A fast full index scan reads the entire index, unsorted, as it exists on disk. It is basically using the index as a “skinny” version of the table. The query in question would be accessing only attributes in the index. (We are not using the index as a way to get to the table—we are using the index instead of the table.) We use multiblock I/O and read all the leaf, branch, and root blocks. We ignore the branch and root blocks when executing the query and just process the (unordered) data in the leaf blocks.

A full index scan reads the index a block at a time, from start to finish. It reads the root block, navigates down the left-hand side of the index (or the right-hand side for a descending full scan), and then when it hits the leaf block, it reads across the entire bottom of the index—a block at a time—in sorted order. It uses single-block, not multiblock, I/O for this operation.


Reference from "Ask Tom"