Indexing NULL Value in a Table Column

As we know, a normal index does not include NULL value, so SQL query won’t use this normal index for a SQL query with condition ‘IS NULL’.

Here is a tip for how to create an index on column with NULL value.

SQL> create index IDX_BATCH_ID_NULL on test ( BATCH_ID, 1);

Index created.

Actually the index is created by including two columns of BATCH_ID and constant number 1.

SQL> select count(*) from test where BATCH_ID is null;

COUNT(*)
----------
292

SQL> select count(*) from test where BATCH_ID is not null;

COUNT(*)
----------
172

SQL> create index idx_BATCH_ID on test(BATCH_ID);

Index created.

SQL> SET AUTOTRACE ON EXPLAIN

SQL> select MACHINE_ID from test 
     where BATCH_ID is not null and rownum<=2;

MACHINE_ID
----------------------
MACHINE_1
MACHINE_2

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773

--------------------------------------------------------------------
| Id | Operation          | Name|Rows| Bytes | Cost(%CPU)| Time     |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT   |     |   2|   130 |    2   (0)| 00:00:01 |
|* 1 |  COUNT STOPKEY     |     |    |       |           |          |
|* 2 |   TABLE ACCESS FULL|TEST | 172| 11180 |    2   (0)| 00:00:01 |
---------------------------------------------------------------------


SQL> select MACHINE_ID from test 
     where BATCH_ID is null and rownum<=2;

MACHINE_ID
---------------------------------------------
MACHINE_3
MACHINE_4

Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773

-----------------------------------------------------------------
|Id| Operation        |Name|Rows|Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------
|0 | SELECT STATEMENT |    | 2  |  650 | 2 (0)      | 00:00:01 |
|*1| COUNT STOPKEY    |    |    |      |            |          |
|*2| TABLE ACCESS FULL|TEST|292 |18980 | 2 (0)      | 00:00:01 |
-------------------------------------------------------------------

SQL> create index idx_BATCH_ID_null on test ( BATCH_ID, 1);

Index created.

SQL> select MACHINE_ID from test 
    where BATCH_ID is null and rownum<=2;

MACHINE_ID
------------------------------------------------------
MACHINE_3
MACHINE_4


Execution Plan
----------------------------------------------------------
Plan hash value: 1426346817

--------------------------------------------------------------------
|Id| Operation                   |Name|Rows|Bytes|Cost(%CPU)| Time |
---------------------------------------------------------------------
|0 |SELECT STATEMENT             |    |  2 | 130 |  3   (0)|00:00:01|
|*1| COUNT STOPKEY               |    |    |     |         |        |
| 2|  TABLE ACCESS BY INDEX ROWID|TEST|292 |18980|  3   (0)|00:00:01|
|*3|   INDEX RANGE SCAN          | IDX_BATCH_ID_NULL 
                                      | 23 |     |  2   (0)|00:00:01|
----------------------------------------------------------------------