In Oracle Database 19c, the Cost-Based Optimizer (CBO) selects the most efficient method to retrieve data. These methods are known as SQL access paths.
One of these access paths is the Index Full Scan. In this method, Oracle reads all entries of an index, but in index order, instead of scanning the table.
This access path is useful when Oracle needs to process all index entries, especially when the query requires sorted data.
1. What is an Index Full Scan?
An Index Full Scan occurs when Oracle reads the entire index structure sequentially, from the root block through the branch blocks to the leaf blocks, following the index key order.
Unlike Index Range Scan, which reads only part of the index, Index Full Scan reads the entire index.
Example query:
SELECT * FROM employees ORDER BY employee_id;
If employee_id is indexed, Oracle may scan the entire index to return rows already sorted.
2. Execution Plan Example
EXPLAIN PLAN SET statement_id = 'wadhah_sql_index_full_scan' FOR select salary from employees order by employee_id;
Example output:
This means Oracle reads all entries from the index.
3. How Index Full Scan Works
The process works as follows:
- Oracle starts from the root block of the index
- Traverses the branch blocks
- Reads all leaf blocks sequentially
- Retrieves values in sorted index order
Because B-tree indexes are already sorted, Oracle can avoid an additional sorting operation.
4. When Oracle Uses Index Full Scan
Oracle may choose an Index Full Scan in several situations.
4.1. ORDER BY Optimization:
When the query requires sorting based on an indexed column.
4.2. Query Uses Only Indexed Columns:
If the query retrieves only columns present in the index, Oracle may use the index without accessing the table.
4.3. When Index is Smaller than Table
Indexes are usually smaller than tables, so scanning the entire index may be faster than performing a Full Table Scan.
5. Performance Characteristics
Index Full Scan characteristics:
- Reads all index entries
- Maintains sorted order
- Uses single-block I/O
- May still access the table via ROWID
Google+
Facebook
