What is Fast Full Index Scan vs. Index Range Scan

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 queries.
  • 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 circumstances:

  • 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.