Index Full Scans:

An index full scan reads the entire index in order. An index full scan can eliminate a separate sorting operation because the data in the index is ordered by index key.

The database uses single-block I/O rather than multiblock I/O.

Situations where optimizer chooses Index Full Scans:

• A predicate references a column in the index. This column need not be the leading column.
• No predicate is specified and all columns in the table and in the query are in the index.
• No Predicate is specified and at least one indexed column is not null.
• A query includes an ORDER BY on indexed non-nullable columns.

Example:

SELECT department_id, department_name FROM departments ORDER BY department_id;

SQL_ID  94t4a20h8what, child number 0
-------------------------------------
select department_id, department_name from departments order by department_id

Plan hash value: 4179022242

--------------------------------------------------------------------------------
|Id | Operation                   | Name        |Rows|Bytes|Cost(%CPU)|Time    |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |    |     | 2 (100)|          |
| 1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2   (0)| 00:00:01 |
| 2 |   INDEX FULL SCAN           | DEPT_ID_PK  | 27 |     | 1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

For each index entry, the database obtains the rowid from the entry, and then retrieves the table row specified by the rowid.