Database Reference
In-Depth Information
----------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 527 |
| 1 | TABLE ACCESS BY INDEX ROWID
BATCHED
| T | 1 | 527 |
|* 2 | INDEX RANGE SCAN | I_N1 | 1 | 527 |
----------------------------------------------------------------------
2 - access("N1"=6)
Per default, an index scan is performed in ascending order. Therefore, the
index
hint instructs the query
optimizer to behave in that way also. To explicitly specify the scan order, it's possible to use the
index_asc
and
index_desc
hints. The following query shows how. In the execution plan, the scan in descending order is shown
through the
INDEX RANGE SCAN DESCENDING
operation:
SELECT /*+
index_desc
(t) */ * FROM t WHERE n1 = 6
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 527 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 527 |
|* 2 |
INDEX RANGE SCAN DESCENDING
| I_N1 | 1 | 527 |
---------------------------------------------------------------
2 - access("N1"=6)
filter("N1"=6)
Note that
INDEX RANGE SCAN
and
INDEX RANGE SCAN DESCENDING
return the same data. Only the order is
different. Later on, the section on range conditions describes when such an access path is useful.
Equality Conditions and Bitmap Indexes
With bitmap indexes, equality conditions are carried out in three operations. In order of execution, the first operation
is
BITMAP INDEX SINGLE VALUE
, which scans the index and applies the restriction. As the name suggests, this
operation looks for a single value. The second operation,
BITMAP CONVERSION TO ROWIDS
, converts the bitmaps it gets
from the first operation into a list of rowids. The third operation accesses the table with the list of rowids built by the
second operation. Notice that all three operations are executed only once:
SELECT /*+ index(t i_n4) */ * FROM t WHERE n4 = 6
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 527 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 527 |
| 2 |
BITMAP CONVERSION TO ROWIDS
| | 1 | 527 |
|* 3 |
BITMAP INDEX SINGLE VALUE
| I_N4 | 1 | 1 |
---------------------------------------------------------------
