Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

IS NULL Conditions and B-tree Indexes

17 February 2011 2 Comments Written by Christian Antognini

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))
10gR1, 10gR2, 11gR1, 11gR2, 9iR2, Indexes, Query Optimizer, TOP
Edition-Based Redefinition (update)
Ad: Trivadis Performance Days 2011

2 Comments

  1. Emmanuel Humblot Emmanuel Humblot
    18 February 2013    

    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

    Reply
    • Christian Antognini Christian Antognini
      18 February 2013    

      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

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.