As the name suggests, Index Full Scans are Index Scans where the entire index is scanned in order.

Following are the 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.

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