Oracle Index Full Scan: How It Works and When the Optimizer Uses It

A production AWR report flagged a query with an INDEX FULL SCAN operation consuming significant I/O. The developer assumed it was an index range scan gone wrong. It was not. The optimizer had a reason — and understanding that reason is what separates reactive troubleshooting from deliberate tuning.

This post breaks down what Oracle index full scan actually does, when the optimizer chooses it, how to read it in an execution plan, and how it compares to index fast full scan.

---

What Is Index Full Scan?

Index full scan is an optimizer access path where Oracle reads the entire index structure — but in a very specific way.

The database starts at the root block of the B-tree index, navigates down through the branch blocks to the leftmost leaf block, and then reads every leaf block sequentially from left to right using single block I/O.

The key characteristics:

• Single block I/O — one block read at a time, not multiblock
• Sorted output — data comes back in index key order because it follows the leaf chain
• No table access required — if all columns the query needs are in the index, Oracle never touches the table

This is fundamentally different from a full table scan where Oracle reads every data block. Here, Oracle reads every index block — which is almost always smaller than the table itself.

---

When Does the Optimizer Choose Index Full Scan?

Oracle does not pick index full scan randomly. It chooses this path when all of these conditions are true:

1. The query can be satisfied entirely from the index

Every column in the SELECT, WHERE, and ORDER BY clause exists in the index. No table access needed.

2. The query needs sorted output

Because index full scan returns data in sorted order, Oracle can skip a costly SORT ORDER BY operation. If the query has an ORDER BY on the indexed columns, the optimizer may prefer index full scan to avoid sorting in memory or temp.

3. The index is relatively small compared to the table

If the index has significantly fewer blocks than the table, reading the entire index with single block I/O is cheaper than a full table scan followed by a sort.

A common real-world scenario:

sql

SELECT employee_id, salary
FROM   employees
ORDER BY employee_id;

If there is an index on (employee_id, salary), Oracle can satisfy this entirely from the index in sorted order — no table access, no sort operation.

---

Reading Index Full Scan in an Execution Plan

Here is what it looks like in an execution plan:

sql

EXPLAIN PLAN FOR
SELECT employee_id, department_id
FROM   employees
ORDER BY employee_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost | 
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |   107 |   642 |    1 |
|   1 |  INDEX FULL SCAN | EMP_DEPT_IX   |   107 |   642 |    1 |
---------------------------------------------------------------------------

Notice:

• No TABLE ACCESS step — everything comes from the index
• No SORT ORDER BY step — the index full scan delivers sorted data natively
• Cost is low — single block I/O on a small index is cheap

Index Full Scan vs Index Fast Full Scan

This is the question every DBA asks. Both scan the entire index, but the mechanism is completely different.

Aspect	Index Full Scan	Index Fast Full Scan
I/O Type	Single block I/O	Multiblock I/O
Read Order	Leaf chain (left to right)	Physical disk order
Output Sorted?	Yes — index key order	No — random order
Avoids Sort?	Yes	No
Parallelism	No	Yes — can use parallel query
Use Case	Query needs sorted data from index	Query needs all rows, order does not matter

The optimizer’s decision logic is straightforward:

If the query needs data in sorted order and the entire result can come from the index — index full scan. If the query does not care about order and just needs all the data from the index — index fast full scan, because multiblock I/O is faster for bulk reads.

---

How to Force or Avoid Index Full Scan

Force it with a hint:

sql

SELECT /*+ INDEX(employees emp_dept_ix) */ employee_id, department_id
FROM   employees
ORDER BY employee_id;

The INDEX hint tells the optimizer to use the specified index. Combined with an ORDER BY on the indexed columns, this typically results in an index full scan.

Avoid it:

If Oracle is choosing index full scan when you want a different path, consider:

• Adding a NO_INDEX hint to force a full table scan instead
• Checking if a SORT ORDER BY would actually be cheaper — sometimes the sort in memory is faster than single block I/O across thousands of leaf blocks
• Checking index statistics — stale statistics can mislead the optimizer into choosing the wrong path

sql

-- Check when index stats were last gathered
SELECT index_name, last_analyzed, num_rows, leaf_blocks
FROM   user_indexes
WHERE  index_name = 'EMP_DEPT_IX';

---

When Index Full Scan Becomes a Problem

Index full scan is efficient on small to medium indexes. It becomes expensive when:

• The index has thousands of leaf blocks — single block I/O on a large index is slow
• The buffer cache cannot hold the index — repeated physical reads instead of logical reads
• The query runs frequently — each execution re-traverses the entire leaf chain

In these cases, check whether a fast full scan with a sort, or a different index design, would be more efficient. The AWR report will show you the wait events — if you see heavy db file sequential read waits on the index segments, the index full scan is the culprit.

---

Key Takeaways

Oracle index full scan reads every leaf block sequentially using single block I/O and returns data in sorted order. The optimizer chooses it when the query needs sorted results and all required columns exist in the index. It eliminates both table access and sort operations, making it extremely efficient for the right workload — but expensive on large indexes where multiblock I/O (index fast full scan) would be faster.

Understanding when and why Oracle picks this path is what turns a reactive DBA into a proactive one.

---

Related posts:

• Optimizer Access Paths: Fast Full Scan
• Optimizer Access Paths: Index Unique Scan
• Optimizer Access Paths: How Oracle Database Works

Sanjeeva Kumar: last updated on April 15, 2026