Database Reference
In-Depth Information
As the following example shows, the very same execution plan is also used when the
IS NULL
condition is
specified for the leading column of the index:
SELECT /*+ index(t) */ * FROM t WHERE
n1 IS NULL
AND n2 = 8
----------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4 |
|* 2 |
INDEX RANGE SCAN
| I_N123 | 1 | 4 |
----------------------------------------------------------------
2 - access("N1" IS NULL AND "N2"=8)
filter("N2"=8)
The other case where
IS NULL
conditions can be applied through composite B-tree indexes is when the
NULL
values are guaranteed to be stored in the index. The following is a query taking advantage of this case. Notice
that, because of the
n2 IS NOT NULL
condition, it's guaranteed that all rows fulfilling the
WHERE
clause have a
corresponding index entry in the
i_n123
index. As a result, an index range scan can be used to find them:
SELECT /*+ index(t) */ * FROM t WHERE
n1 IS NULL
AND
n2 IS NOT NULL
----------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 521 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 521 |
|* 2 |
INDEX RANGE SCAN
| I_N123 | 1 | 521 |
----------------------------------------------------------------
2 - access("N1" IS NULL)
filter("N2" IS NOT NULL)
The
NULL
values are also guaranteed to be stored in a composite index when at least one of the columns isn't
nullable. A special case satisfying this condition is an index created on a nullable column and a constant value. It goes
without saying that this is a trick. But, in some situations, it's a useful one. The following index shows an example
(notice that instead of the “0”, another value could have been used):
CREATE INDEX i_n1_nn ON t (n1, 0)
With such an index in place, a query like the following is able to carry out an index range scan:
SELECT /*+ index(t) */ * FROM t WHERE n1 IS NULL
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 526 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 526 | 526 |
|* 2 |
INDEX RANGE SCAN
| I_N1_NN | 1 | 526 | 526 |
--------------------------------------------------------------------------
