Database Reference
In-Depth Information
Range Conditions and Bitmap Indexes
With bitmap indexes, range conditions are carried out in a similar way as equality conditions. The only difference is
that the
BITMAP INDEX RANGE SCAN
operation is used instead of the
BITMAP INDEX SINGLE VALUE
operation:
SELECT /*+ index(t (t.n4)) */ * FROM t WHERE n4 BETWEEN 6 AND 19
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6840 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 6840 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | 6840 |
|* 3 | BITMAP INDEX RANGE SCAN | I_N4 | 1 | 13 |
---------------------------------------------------------------
3 - access("N4">=6 AND "N4"<=19)
With bitmap indexes, because there's no concept of ascending and descending scans, it's not possible to avoid,
and thereby optimize,
ORDER BY
operations.
IN Conditions
IN
conditions don't have a specific access path. Instead, in the execution plan, the
INLIST ITERATOR
operation
points out that part of the execution plan is executed several times because of an
IN
condition. The following three
queries show how the operation used for the index scan itself depends on the index type. The first is a unique index,
the second is a nonunique B-tree index, and the third is a bitmap index. Basically, an
IN
condition is just a series of
equality conditions. Note that operations related to the index and table access are executed once for each value in the
IN
list (see the
Starts
column):
SELECT /*+ index(t t_pk) */ * FROM t WHERE
id
IN (6, 8, 19, 28)
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 |
| 1 |
INLIST ITERATOR
| | 1 | 4 |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
4
| 4 |
|* 3 | INDEX UNIQUE SCAN | T_PK |
4
| 4 |
---------------------------------------------------------------
3 - access(("ID"=6 OR "ID"=8 OR "ID"=19 OR "ID"=28))
