In Oracle Database 19c, the Cost-Based Optimizer (CBO) determines the most efficient method to retrieve data from tables. These methods are called SQL access paths.
One of the important index access paths is the Index Fast Full Scan (IFFS). In this method, Oracle scans all blocks of an index, similar to a Full Table Scan, but it reads only the index structure instead of the table.
This access path is commonly used when all required columns exist in the index, allowing Oracle to retrieve data without accessing the table.
1. What is an Index Fast Full Scan?
An Index Fast Full Scan occurs when Oracle reads the entire index using multiblock I/O, ignoring the logical order of the index.
Unlike Index Full Scan, which preserves the index order, Index Fast Full Scan reads index blocks quickly without maintaining sorting.
Example query:
SELECT COUNT(*) FROM employees;
If a suitable index exists, Oracle may scan the index instead of the table.
2. Execution Plan Example
EXPLAIN PLAN FOR
SELECT COUNT(employee_id)
FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Example output:
| Id | Operation | Name |
|----|-----------------------|--------------|
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX FAST FULL SCAN | EMP_ID_IDX |
This indicates Oracle scanned all index blocks using a Fast Full Scan.
3. How Index Fast Full Scan Works
The process works as follows:
- Oracle reads all index blocks
- Uses multiblock reads (similar to Full Table Scan)
- Does not follow the index tree order
- Processes rows directly from the index
Because indexes are usually smaller than tables, this operation can be faster than scanning the entire table.
4. When Oracle Uses Index Fast Full Scan
Oracle may choose this access path in the following situations.
1. Query Uses Only Indexed Columns
Example:
SELECT employee_id
FROM employees;
If employee_id is indexed, Oracle may read only the index.
2. Aggregate Operations
Example:
SELECT COUNT(employee_id)
FROM employees;
Oracle may count rows using the index instead of scanning the table.
3. Index Covers All Required Columns
Example index:
CREATE INDEX emp_cover_idx
ON employees(employee_id, department_id);
Query:
SELECT employee_id, department_id
FROM employees;
Oracle may use Index Fast Full Scan because all required columns exist in the index.
5. Performance Characteristics
Index Fast Full Scan has the following characteristics:
- Reads entire index
- Uses multiblock I/O
- Does not preserve index order
- Can support parallel execution
Because it uses multiblock reads, it is typically faster than Index Full Scan.
Google+
Facebook
