Indexing NULL values

Environment: 

Oracle database 11.2.0.4

Solution:

Create an index and add a constant to the end of the index so NULL values are stored.

For the following statement and regular index on PAYMENT_CLASSIFICATION_ID
select * from CDR.F_PAYMENT_ITEM where PAYMENT_CLASSIFICATION_ID is null;

Plan hash value: 2353806608
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   434 | 38626 |   423K  (1)| 01:24:38 |
|*  1 |  TABLE ACCESS FULL| F_PAYMENT_ITEM |   434 | 38626 |   423K  (1)| 01:24:38 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    1 - filter("PAYMENT_CLASSIFICATION_ID" IS NULL)


DROP INDEX CDR.FPI_PAYMNT_CLS_ID;

CREATE INDEX CDR.FPI_PAYMNT_CLS_ID ON CDR.F_PAYMENT_ITEM
(PAYMENT_CLASSIFICATION_ID,1);

Plan with the new index:
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   434 | 38626 |    82   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| F_PAYMENT_ITEM    |   434 | 38626 |    82   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FPI_PAYMNT_CLS_ID |   434 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PAYMENT_CLASSIFICATION_ID" IS NULL)


Index type of the new index is FUNCTION-BASED NORMAL (from DBA_INDEXES).
One of disadvantages of Function-Based Indexes, according to Database Advanced Application Developer's Guide, chapter 4, Using Indexes in Database Applications:
- The database does not use function-based indexes when doing OR expansion.

but for this particular index, it works fine:
select * from CDR.F_PAYMENT_ITEM where PAYMENT_CLASSIFICATION_ID in (122,1);

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |  2756 |   239K|   497   (0)| 00:00:06 |
|   1 |  INLIST ITERATOR             |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| F_PAYMENT_ITEM    |  2756 |   239K|   497   (0)| 00:00:06 |
|*  3 |    INDEX RANGE SCAN          | FPI_PAYMNT_CLS_ID |  2756 |       |    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PAYMENT_CLASSIFICATION_ID"=1 OR "PAYMENT_CLASSIFICATION_ID"=122)