Re: Bitmap Index

First off, I agree with this estimate. On initial use, I was told that Bitmap indices were only useful for 2-5 distinct values (which I know to be silly), but I always found a break-even point at around 50-ish. I work primarily in DSS/Data Warehousing applications, so I'm not sure about OLTP requirements.

The main reason I'm following up on this is to echo the concerns about statistics. We ran partial test sets (2-10% of data volume) and things worked pretty nicely. When we loaded the full set (45M row central "fact" table), things went very poorly until we deleted the statistics from each table and re-analyzed them.

Dave
DS Solutions

A.E.Vervaeke wrote:

> A good rule of thumb is if the number of distinct values you would be
> indexing make up less than 2-3 percent of the total number of rows. For
> example, if you have a table with 100,000 rows, you could have a bitmap
> index of 2-3 thousand values. We used it for 4200 distinct values in a table
> of 30 million rows. One other thing, in 7.3 we found that to make the
> optimizer work correctly, every time we went to analyze the table, we first
> had to delete statistics, and then estimate statistics. If we did not delete
> first, the optimizer did not work as it should. I hope that this helps.
>
> Alan V.
>
> Marcel Romahn wrote in message <34EAAB1C.28E5B8C9_at_messer.de>...
> >Conditions for using bitmap indexes essentialy are the low cardinality
> >of the indexed columns with low selectivity, which means huge amout of
> >retrieved rows.
> >
> >What is "low cardinality" in this context. Yes, a GENDER or YES/NO
> >column is easy to understand. But what about attributes with 20 or 30
> >different values. Where is the break even point in comparison with
> >b-tree indexes.

<snip>

> >Marcel Romahn
> >
Received on Wed Feb 18 1998 - 00:00:00 CST