Database Reference
In-Depth Information
■
In this section, to force index scans, the
index
hint is used by specifying both the name of the table and the
name of the index. In other words, the hint specifies which index should be used. When it's used in this way, the hint is
valid only if an index with that name exists. Because the name of an index can be easily changed (for example, with the
ALTER INDEX RENAME
statement), in practice it's also very easy for hints with that syntax to be invalidated by mistake.
Caution
As for B-tree indexes, from version 12.1 onward the operation used to access a table with several rowids returned
from an index scan is
TABLE ACCESS BY INDEX ROWID BATCHED
. The following example shows the execution plan for
the same query as the previous one:
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 BATCHED
| T | 1 | 527 |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | 527 |
|* 3 | BITMAP INDEX SINGLE VALUE | I_N4 | 1 | 1 |
----------------------------------------------------------------------
3 - access("N4"=6)
IS NULL Conditions and B-tree Indexes
With B-tree indexes,
IS NULL
conditions can be applied only through composite B-tree indexes if either another
condition leading to an index range scan is specified or the
NULL
values are guaranteed to be stored in the index.
At least one of these two conditions must be met, because index entries that only have
NULL
values are neither stored
in a single-column index nor in a composite index.
The following query demonstrates the case where two conditions are specified. The execution plan confirms,
through the access predicate of operation 2, that the condition on the
n2
column is applied using the
i_n123
index.
Also notice that operation 2 returns only 5 rows, whereas for the example in the “Equality Conditions and B-tree
Indexes” section, which doesn't have the
n2 IS NULL
restriction, the range scan returned 527 rows:
SELECT /*+ index(t) */ * FROM t WHERE n1 = 6 AND
n2 IS NULL
----------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 5 |
|* 2 |
INDEX RANGE SCAN
| I_N123 | 1 | 5 |
----------------------------------------------------------------
2 - access("N1"=6 AND "N2" IS NULL)
