posted Aug 4, 2011, 9:47 AM by Sachchida Ojha
In Oracle there are some SQL queries that can be resolved by reading the index without touching the table data.
INDEX FAST FULL SCAN is the equivalent of a FULL TABLE SCAN, but for an index.
It reads using multiblock reads, but results are NOT returned sorted.
For a query to make use of Index FFS the column should be defined as NOT NULL
or at least one column in a composite index is NOT NULL.
- In an index-range scan or full-index scan, index blocks are
read one at a time in key order. In a fast full scan, blocks are read
in the order in which they appear on the disk and the database can
read multiple blocks in a single I/O operation, depending on the value
of the server parameter db_file_multiblock_read_count. Reading
multiple blocks in a single I/O operation can radically reduce the
total number of disk I/Os required, resulting in faster execution of
- The fast full-index scan can be performed in parallel, whereas
an index-range scan or full-index scan can be processed only
serially. That is, the database can allocate multiple processes to
perform a fast full-index scan, but it can use only a single process
for traditional index scans. Using parallel query may also
substantially reduce query-execution time for systems with multiple
CPUs or those with data spread across multiple disk devices.
- Although a full table scan can use parallelism and multiblock
read techniques, the number of blocks in a table is typically many
times as great as the number of blocks in an index. Therefore, a fast
full-index scan usually outperforms an equivalent full-table scan.
Using a fast full-index scan is appropriate in the following
- All columns required to satisfy the query are included in the index.
- At least one of the columns in the index is defined as NOT NULL.
- The query will return more than 10 percent to 20 percent of the rows in the index.