At page 383 of my book I wrote the following sentence (BTW, the same information is also provided by Table 9-3 at page 381):
With B-tree indexes, IS NULL conditions can be applied only through composite B-tree indexes when several SQL conditions are applied and at least one of them is not based on IS NULL or an inequality.
The text continues by showing the following examples (notice that in both cases the IS NULL predicate is applied through an access predicate):
SELECT /*+ index(t) */ * FROM t WHERE n1 = 6 AND n2 IS NULL
Plan hash value: 780655320
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | I_N123 |
----------------------------------------------
2 - access("N1"=6 AND "N2" IS NULL)
SELECT /*+ index(t) */ * FROM t WHERE n1 IS NULL AND n2 = 8
Plan hash value: 780655320
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | I_N123 |
----------------------------------------------
2 - access("N1" IS NULL AND "N2"=8)
filter("N2"=8)
When I wrote that sentence I didn’t think about one case that, according to it, specifically the part “is not based on IS NULL or an inequality”, is not covered. In fact, as the following examples show, it is also possible to apply an IS NULL predicate when the other one is an IS NOT NULL. It is especially interesting to notice that the access predicate doesn’t reference at all the NOT NULL column!
SELECT /*+ index(t) */ * FROM t WHERE n1 IS NULL AND n2 IS NOT NULL
Plan hash value: 780655320
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | I_N123 |
----------------------------------------------
2 - access("N1" IS NULL)
filter("N2" IS NOT NULL)
SELECT /*+ index(t) */ * FROM t WHERE n1 IS NOT NULL AND n2 IS NULL
Plan hash value: 3029444779
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX SKIP SCAN | I_N123 |
----------------------------------------------
2 - access("N2" IS NULL)
filter(("N2" IS NULL AND "N1" IS NOT NULL))
Hi Christian !
It is also possible to search for NULL values through a function-based index on the nvl function.
It is especially useful if NULL values are scarce.
Such a function-based index could be defined this way :
create index fn_idx on t(nvl(t1,’NULLVALUE’));
and to search for NULLs in the t1 column, you could proceed this way :
select /*+INDEX(T FN_IDX) */ * from t where nvl(t1,’NULLVALUE’)=’NULLVALUE’;
Best regards,
Emmanuel
Hi Emmanuel
What you propose works. But, honestly, I find it way better to do the following:
CREATE INDEX i ON t (t1,0);
SELECT * FROM t WHERE t1 IS NULL;
IMO avoiding as many tricks as possible is key.
Cheers,
Chris