Tuning is null query in oracle

When there is some simple query which is using just is null criteria on big table, oracle cost based optimizer making table access full, so query running slow, here is demonstration how to tune similar queries:

create table my_test_table as select * from dba_objects;

insert into my_test_table  select * from dba_objects;
commit;

UPDATE my_test_table set object_name=null where rownum<=100;
commit;

create index my_test_table_i1 on my_test_table(object_name);

begin
  dbms_stats.gather_table_stats(ownname => 'QOBESA',
                                tabname => 'MY_TEST_TABLE',
                                cascade => true);
end;


select * from my_test_table where object_name is null;


------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |  100 | 12500 |  108 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | MY_TEST_TABLE |  100 | 12500 |  108 | 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("OBJECT_NAME" IS NULL)

So we have table with more than 100 000 rows and in 100 rows column object_name is null, which is indexed column, but optimizer doesn't using it and making full table scan, if your table is huge and it contains many gigabytes of data it will be big problem. Many believes that there is no way to tune this type of statement, because oracle does not store NULL values in indexes, but here is little trick to use index on not null columns

create index my_test_table_i2 on my_test_table(object_name, -1);


begin
  dbms_stats.gather_table_stats(ownname => 'QOBESA',
                                tabname => 'MY_TEST_TABLE',
                                cascade => true);
end;


select * from my_test_table where object_name is null;


---------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |  100 | 12900 |   78 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | MY_TEST_TABLE    |  100 | 12900 |   78 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | MY_TEST_TABLE_I2 |  100 |       |    3 | 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("OBJECT_NAME" IS NULL)

So solution is to add some constant to the end of the index to be sure null values are stored.