More fun with nulls and (bitmap) indexes SQL

 


create table so_you_do_not_need_to_do ( conversions_like_this varchar2(100) );

-- stick in some data with just one null value
insert into so_you_do_not_need_to_do
select case when level = 1 then null else level end
from dual
connect by level <= 1000; commit; create bitmap index includes_null_values on so_you_do_not_need_to_do ( nvl(conversions_like_this, 'to find null values via the bm index') ); exec dbms_stats.gather_table_stats(user, 'so_you_do_not_need_to_do', cascade => true);

-- to get the benefit of this index, you have to have the nvl conversion in your where clause
explain plan for
select * from so_you_do_not_need_to_do
where nvl(conversions_like_this, 'to find null values via the bm index') = 'to find null values via the bm index';

select * from table(dbms_xplan.display);

-- if you don't, then you just get a full table scan
explain plan for
select * from so_you_do_not_need_to_do
where conversions_like_this is null;

select * from table(dbms_xplan.display);

drop index includes_null_values;
create bitmap index includes_null_values on so_you_do_not_need_to_do ( conversions_like_this );

exec dbms_stats.gather_table_stats(user, 'so_you_do_not_need_to_do', cascade => true);

-- but if you just create the index "normally", your index scan against null works!
explain plan for
select * from so_you_do_not_need_to_do where conversions_like_this is null;

select * from table(dbms_xplan.display);

drop table so_you_do_not_need_to_do 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)