Oracle => Indexing NULL Values Trick

Sometimes you need to search NULL values on your big tables. 

By default, Oracle doesn’t index an entry if all columns within the index are NULL.

However, it is possible to add NULL values to the index by creating a composite one with a constant value.

create table mytable (myid number, mystring1 varchar2(30), mystring2 varchar2(30));

insert into mytable 
   select level, 
          dbms_random.string('x', 5), 
          dbms_random.string('X', 5) 
   from dual connect by level < 1000000;

insert into mytable values (NULL,dbms_random.string('x', 5), dbms_random.string('X', 5));

create unique index mytable_index1 on mytable (myid);

begin
  dbms_stats.gather_table_stats(ownname          => 'SYS',
                                tabname          => 'MYTABLE',
                                estimate_percent => 100,
                                cascade          => TRUE);
end;

Check the execution plan:

As you see, I have only one row among 1 million rows   which has NULL value for "myid" column but still I have to make FTS for the table.

Now, create another index onsame column but this time also adding a constant value;

create unique index mytable_index2 on mytable (myid,1);

begin
  dbms_stats.gather_table_stats(ownname          => 'SYS',
                                tabname          => 'MYTABLE',
                                estimate_percent => 100,
                                cascade          => TRUE);
end;

Check the execution plan after new index: