Feb 102014
 

Below is a direct copy and paste of some code I ran (nothing is done in between the sections). There’s clearly data in the tables, but it vanishes when they are joined:

insert into theres_a_row_here 
values ('Indeed there is');
insert into and_a_row_here 
values ('Indeed there is');

select * from theres_a_row_here;

BUT_FOR_SOME_REASON
-------------------
Indeed there is                  

select * from and_a_row_here;

THEY_DISAPPEAR_WHEN_JOINED
--------------------------
Indeed there is                         

select but_for_some_reason, 
       they_disappear_when_joined 
from   theres_a_row_here, 
       and_a_row_here
where  but_for_some_reason = 
         they_disappear_when_joined;

no rows selected

Why? As always, put your answers in the comments!

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.
Continue reading »