Index Full Scans

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

One thought on “Index Full Scans

  1. Pingback: Index Fast Full Scans | Oracle Database Internal Mechanism

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s