Database Reference
In-Depth Information
Per default, an index scan is performed in ascending order. Consequently, the
index
hint instructs the query
optimizer to behave in this way also. To explicitly specify the scan order, you can use the
index_asc
and
index_desc
hints. The following query shows how. The scan in descending order is shown in the execution plan:
SELECT /*+
index_desc
(t t_n1_i) */ n1 FROM t WHERE n1 IS NOT NULL ORDER BY n1
DESC
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX FULL SCAN
DESCENDING
| T_N1_I |
---------------------------------------------
1 - filter("N1" IS NOT NULL)
The third case is related to the
count
function. If a query contains it, the query optimizer tries to take advantage
of an index in order to avoid a full table scan. The following query is an example. Notice that the
SORT AGGREGATE
operation is used to execute the
count
function:
SELECT /*+ index_ffs(t t_n1_i) */
count
(n1) FROM t
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 |
INDEX FAST FULL SCAN
| T_N1_I |
----------------------------------------
When a count against a
nullable
column is processed, the query optimizer can pick out any index containing that
column (this is because
NULL
values aren't counted). When either a
count(*)
or a count against a
not-nullable
column
is processed, the query optimizer is able to pick out either any B-tree index that contains at least a not-nullable column
(this is necessary because only in this case the number of index entries is guaranteed to be the same as the number of
rows), or any bitmap index. Therefore, it picks out the smaller index that can be considered.
Even though the examples in this section are based on B-tree indexes, most techniques apply to bitmap indexes
as well. There are only two differences. First, bitmap indexes can't be scanned in descending order (this is a limitation
due to the implementation). Second, bitmap indexes always store
NULL
values. Because of this, they can be used in
more situations than B-tree indexes. The following queries show examples that are analogous to the previous ones:
SELECT /*+
index
(t t_n2_i) */ n2 FROM t WHERE n2 IS NOT NULL
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | BITMAP CONVERSION TO ROWIDS| |
|* 2 |
BITMAP INDEX FULL SCAN
| T_N2_I |
----------------------------------------------
2 - filter("N2" IS NOT NULL)
