Yes, yes you can index nulls SQL

 
create table is_null_checks_against(this_nullable_column integer, x varchar2(10));

insert into is_null_checks_against
  select case when rownum = 1 then null else rownum end, dbms_random.string('x', 10)
  from   dual 
  connect by level <= 100;
  
exec dbms_stats.gather_table_stats(user, 'is_null_checks_against');

explain plan for
 select *
 from   is_null_checks_against
 where  this_nullable_column is null;

-- without the index, we get a full table scan
select * from table(dbms_xplan.display);

create index that_can_be_used on is_null_checks_against(this_nullable_column, 1);

explain plan for
 select *
 from   is_null_checks_against
 where  this_nullable_column is null;

-- with it, we get an "impossible(!)" index range scan!
-- terms and conditions apply: 
-- subject to usual costing analysis, suitable selectivity of data etc.
select * from table(dbms_xplan.display);

drop table is_null_checks_against purge;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)