Query Optimization - Handling NULL Values

Oracle Documentation Says: 
The Oracle database does not include rows in an index if all indexed columns 
are NULL. NULL values are not stored in indexes. Therefore, the following query 
with "COMM IS NULL" will not use an index, even if that COMM is indexed.

Problem:

SQL> CREATE INDEX MYEMP_COMM_INDX ON MYEMP(COMM);
Index created.

SQL> set autot trace
SQL> SELECT * FROM MYEMP WHERE COMM IS NULL;
10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    10 |   390 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYEMP |    10 |   390 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


To use index, the query must be guaranteed not to need any NULL values from the 
indexed expression. So this can be achived by creating an index on NVL(COMM,-1) 
and change query condition accordingly.

Solution 1:

SQL> DROP INDEX MYEMP_COMM_INDX;
Index dropped.

SQL> CREATE INDEX MYEMP_COMM_INDX ON MYEMP(NVL(COMM,-1));
Index created.

SQL> SELECT * FROM MYEMP WHERE NVL(COMM,-1) = -1;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2160314797
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    44 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP           |     1 |    44 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_COMM_INDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------


But if it is not possible to change the query as the module has been deployed which
is using the query. We can actually index NULL values by simply adding another not 
null column to the index.

Solution 2:

SQL> DROP INDEX MYEMP_COMM_INDX;
Index dropped.

SQL> CREATE INDEX MYEMP_COMM_INDX ON MYEMP(COMM,1);
Index created.

SQL> SELECT * FROM MYEMP WHERE COMM IS NULL;
10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2160314797
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    10 |   390 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP           |    10 |   390 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_COMM_INDX |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------


Get More Query Optimization Tips:
http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html