SQL Tuning: Handling nulls in indexes and get them used

Hello,

Thanks to my colleague, I have just buyed the following from my Friend Naga Satish from his email and sharing here.

"Edited Explain plan"
------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |  3626K|   390M|       |  1217K  (1)| 04:03:28 |
|*  1 |  HASH JOIN RIGHT SEMI|           |  3626K|   390M|   964M|  1217K  (1)| 04:03:28 |
|*  2 |   TABLE ACCESS FULL  | TEST_ISID |    19M|   746M|       |   452K  (2)| 01:30:33 |
|*  3 |   HASH JOIN          |           |    46M|  3160M|   749M|   499K  (1)| 01:39:49 |
|*  4 |    TABLE ACCESS FULL | TEST_MKIS |    16M|   558M|       |   120K  (2)| 00:24:03 |
|*  5 |    TABLE ACCESS FULL | TEST_MIXR |    46M|  1624M|       |   213K  (1)| 00:42:37 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("G"."INSTR_ID"="A"."INSTR_ID" AND "G"."ISID_OID"="B"."ISID_OID")
       filter("G"."MKT_OID""A"."MKT_OID")
   2 - filter("G"."BEND_TMS" IS NULL AND ("G"."ID_CTXT_TYP"=' ' OR
              "G"."ID_CTXT_TYP"=' ' OR "G"."ID_CTXT_TYP"=' ' OR
              "G"."ID_CTXT_TYP"=' ' OR "G"."ID_CTXT_TYP"=' ' OR
              "G"."ID_CTXT_TYP"=' ' OR "G"."ID_CTXT_TYP"='RIC' OR
              "G"."ID_CTXT_TYP"='' 
              OR "G"."ID_CTXT_TYP"='' OR
              "G"."ID_CTXT_TYP"=''))
   3 - access("A"."MKT_ISS_OID"="B"."MKT_ISS_OID")
   4 - filter("A"."BEND_TMS" IS NULL)
   5 - filter("B"."BEND_TMS" IS NULL)

Index Information:-

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------- 
TEST_ISID                      TEST_ISID_U001                 BEND_TMS
TEST_MIXR                      TEST_MIXR_IND2                 BEND_TMS
TEST_MIXR                      TEST_IND                       BEND_TMS

Though indexes were placed why the optimizer has not choosen the FTS over index scan?

Issue is that the filter predicates at 2,4,5 (where conditions) is trying to pull null
values from a column, where logically, In general, Oracle btree will not store the index entries
for the null values to make the index structure smaller,
but for bitmap indexes nulls always stored.

Possible Solutions

To fix the same, Possible solutions would be,

1.Define a composite index with at least one other column 
  that has a NOT NULL constraint ideally, the column in which the NULL values 
  might appear would be the leading column in the composite index.
SQL> CREATE INDEX INDEX1 ON TEST_MIXR(BEND_TMS,C2);

2.Define a composite index with a numeric constant (such as 1) 
  as the second column in the composite index.
SQL> CREATE INDEX INDEX1 ON TEST_MIXR(BEND_TMS,1);

3.Bitmap indexes always store NULL values 
  if appropriate (column experiences few updates, deletes, inserts, 
  and an Enterprise Edition database), 
  create a bitmap index for the column.
SQL> CREATE BITMAP INDEX1 ON TEST_MIXR(BEND_TMS);

4.If the number of NULL values in a column will be relatively small 
  (compared to the number of rows in the table), 
  and the original SQL statement may be modified, 
  create a function based index that converts NULL values to 1 
  and non-NULL values to NULL:
SQL> CREATE INDEX INDEX1 ON TEST_MIXR DECODE(BEND_TMS,NULL,1);

5) Another solution would be add a null space as a second column 
SQL> CREATE INDEX INDEX1 ON TEST_MIXR DECODE(BEND_TMS,' '); 

After implementing the fix, FULL TABLE SCAN disappeared and index range scan has been used.

"Edited explain plan"
------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |  3626K|   390M|       |  1217K  (1)| 00:14:00 |
|*  1 |  HASH JOIN RIGHT SEMI|           |  3626K|   390M|   964M|  1217K  (1)| 00:14:00 |
|*  2 |   INDEX RANGE SCAN   | INDEX3    |     9M|   346M|       |   452K  (2)| 00:05:00 |
|*  3 |   HASH JOIN          |           |    11M|   482M|   749M|   499K  (1)| 00:09:49 |
|*  4 |    INDEX RANGE SCAN  | INDEX1    |     5M|    58M|       |   120K  (2)| 00:05:03 |
|*  5 |    INDEX RANGE SCAN  | INDEX2    |     6M|   424M|       |   213K  (1)| 00:04:37 |
------------------------------------------------------------------------------------------

References:-
Charles hooper Post
Richard Foote's Post

-Thanks
Naga