Re: Bitmap Join Index

On Feb 9, 9:17 am, "aravind.ka..._at_gmail.com" <aravind.ka..._at_gmail.com> wrote:
> I am working on creating a Bitmap Join Index and it does not seem to
> be working. Before I post the sql & explain plan here, I have few
> doubts that I'd like to get out of the way. These are based on some of
> my observations and so they could be wrong.
>
> Bitmap Join Index in turn creates Bitmap indexes on the underlying
> tables. Since bitmap indexes are mostly meant for low cardinality
> columns, a bitmap join index which uses a join condition using a high
> cardinality column may not achieve desire results. For example, if I
> create a BMJ on Customers & States, it is likely to work since the
> states cannot be more 50. Whereas if I create BMJ on parts & suppliers
> where the unique identifiers can exceed potentially thousands of
> records, a BMJ does not make sense. Is that right?
>
> When a query includes a where clause on a column other than those
> mentioned in the BMJ, then the BMJ is not invoked by the Optimizer.
> Is that correct? For example, if I create BMJ on Customer_summary &
> Customer, If I run a query to filter on a particular customer, lets
> say
>
> select * from customer_summary, customer where customer_id =
> customer_id and customer_nm = 'Mr.AAA'
>
> This is not going to work since there are additional filters and the
> optimizer may skip the BMJ. Is this correct?
>
> Pl. help clear my misconceptions, if any.

Aravind,

You might want to read the excellent article by Jonathan Lewis on bitmap join
indexes.

http://www.jlcomp.demon.co.uk/07_bitmap_3i.html

If you have further questions after reading the article, you might want to post them here..

Anurag Received on Fri Feb 09 2007 - 11:27:36 CST