Sep 092013
 
create bitmap index can_be_constructed
on     the_join_of_tables (
  eliminating_a_table
)
from   the_join_of_tables, 
       you_frequently_do
where  your_need_for_speed = insatiable;

A rarely used feature in my experience, bitmap join indexes can work well 
to eliminate joins from dimensions to fact tables in data warehouses, 
provided you don't need to update both tables in the join at the same time!

For a more detailed discussion, you can see aritles from Richard Niemiec here and here. For a quick script of the above, keep reading.

create table the_join_of_tables ( 
  your_need_for_speed integer primary key, 
  filler varchar2(10) 
);

create table you_frequently_do ( 
  insatiable integer primary key, 
  eliminating_a_table integer 
);

insert into the_join_of_tables
  select rownum, dbms_random.string('x', 10)
  from   dual
  connect by level <= 1000;

insert into you_frequently_do
  select rownum, 1000-rownum
  from   dual
  connect by level <= 1000;

begin
  dbms_stats.gather_table_stats(user, 
    'the_join_of_tables', cascade => true);
  dbms_stats.gather_table_stats(user, 
    'you_frequently_do', cascade => true);
end;
/

--in the normal query, we access both tables
explain plan for
  select the_join_of_tables.*
  from   the_join_of_tables, 
         you_frequently_do
  where  your_need_for_speed = insatiable 
  and    eliminating_a_table = 1;

select * from table(dbms_xplan.display);

-- make the join index
create bitmap index can_be_constructed
on     the_join_of_tables (
  eliminating_a_table
)
from   the_join_of_tables, 
       you_frequently_do
where  your_need_for_speed = insatiable;

-- and we now only use the index and 
-- "the_join_of_tables" table
explain plan for
  select the_join_of_tables.*
  from   the_join_of_tables, 
         you_frequently_do
  where  your_need_for_speed = insatiable 
  and    eliminating_a_table = 1;

select * from table(dbms_xplan.display);

drop table the_join_of_tables purge;

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