General rule for creating indexes in Oracle is following:

• Bitmap indexes – always index nulls
• B*Tree cluster indexes – always indexes nulls
• B*Tree indexes – can’t index data if all indexed columns are NULL

The last one can cause some performance issues but there is trick to index all NULL columns also for B*Tree indexes.

Following example shows problem

DROP TABLE test_tbl;

CREATE TABLE test_tbl
(
  id1 NUMBER
);

CREATE INDEX test_tbl_idx ON test_tbl(id1);

INSERT INTO test_tbl VALUES(5);
INSERT INTO test_tbl VALUES(NULL);

COMMIT;

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST_TBL', cascade=>TRUE);

EXPLAIN PLAN FOR
SELECT * 
  FROM test_tbl 
 WHERE id1=5;

SELECT * FROM TABLE(dbms_xplan.display(format=>'BASIC'));

-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX RANGE SCAN| TEST_TBL_IDX |
-----------------------------------------

EXPLAIN PLAN FOR
SELECT * 
  FROM test_tbl 
 WHERE id1 IS NULL;
SELECT * FROM TABLE(dbms_xplan.display(format=>'BASIC'));

----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS STORAGE FULL| TEST_TBL |
----------------------------------------------

Solution add dummy value in the end of index

DROP INDEX test_tbl_idx;

CREATE INDEX test_tbl_idx ON test_tbl(id1, 0);

EXPLAIN PLAN FOR
SELECT * 
  FROM test_tbl 
 WHERE id1 IS NULL;

SELECT * FROM TABLE(dbms_xplan.display(format=>'BASIC'));

-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX RANGE SCAN| TEST_TBL_IDX |
-----------------------------------------

Have a fun 🙂

Tomasz