Databases Reference
In-Depth Information
SQL> select * from test where a=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2199568159
| Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
--------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| 113| 2 (0)|0:00:01|
| 1| TABLE ACCESS BY INDEX ROWID|TEST | 1| 113| 2 (0)|0:00:01|
|* 2| INDEX UNIQUE SCAN |TEST_PK1| 1| | 1 (0)|0:00:01|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=2000)
SQL>
In this example, the database uses the primary key on column A (
TEST_PK1
). Note that the database is
likely to perform an index unique scan when you specify all columns of a unique index as well.
Index Range Scan
The database performs an index range scan when it needs to access highly selective data. The database
returns the values of the indexed column in ascending order. If the indexed column is identical for all
rows, the output is ordered according to
ROWID
.
The optimizer chooses an index range scan when dealing with the following types of conditions,
where col1 is the leading column for an index:
col1 =:b1
col1 < :b1
col1 > :b1
Additionally, any
AND
combinations of these three conditions involving a leading column in an index will
result in an index range scan.
Index data is stored in an ascending order. If the database needs to return data in a descending
order, as when it needs to return the latest data first or when it needs to retrieve values less than a
specific value, the database uses an index range scan descending.
