Database Reference
In-Depth Information
Single-column indexes, however, can't be used to apply
IS NULL
conditions. This is because the
NULL
values
aren't stored in the index. Therefore, the query optimizer is simply unable to take advantage of an index range scan in
such a case. Even if you try to force its utilization with the
index
hint, a full table scan or a full index scan is performed:
SELECT /*+ index(t i_n1) */ * FROM t WHERE n1 IS NULL
----------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 526 |
|* 1 | TABLE ACCESS FULL| T | 1 | 526 |
----------------------------------------------------
1 - filter("N1" IS NULL)
IS NULL Conditions and Bitmap Indexes
With bitmap indexes,
IS NULL
conditions are carried out in the same way as equality conditions. This is possible
because the bitmap index stores
NULL
values in the same way as any other value:
SELECT /*+ index(t i_n4) */ * FROM t WHERE
n4 IS NULL
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 526 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 526 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | 526 |
|* 3 |
BITMAP INDEX SINGLE VALUE
| I_N4 | 1 | 1 |
---------------------------------------------------------------
3 - access("N4" IS NULL)
Range Conditions and B-tree Indexes
With B-tree indexes, range conditions are carried out in the same way as equality conditions on nonunique indexes,
or, in other words, with the
INDEX RANGE SCAN
operation. For range conditions, the index type (that is, its uniqueness)
isn't relevant. Because it's a range scan, several rowids could always be returned. For example, the following query
shows a range condition that is applied to the column that the primary key consists of:
SELECT /*+ index(t (t.id)) */ * FROM t WHERE id BETWEEN 6 AND 19
--------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 |
|* 2 | INDEX RANGE SCAN | T_PK | 1 | 14 |
--------------------------------------------------------------
