Re: Re bitmap index

There is a problem with trying to use bitmap indexes on columns that have too low of a cardinality. If the cardinality is too low, it is better to do a full table scan. The "Gender" flag is a perfect example of a case where I normally wouldn't use a bitmap index. The reason for this is distribution and spread.

If I have a Gender flag, it is normally safe to assume that there will be approx 50% Male and 50% Female. Further assume that the data is evenly distributed throughout the table. If a db_block in my table holds two rows, one row will mostly likely contain 'M' while the other row most likely contains 'F'. I still have to read this entire block to get the data I'm looking for. If the block contains two rows with both 'M' values, then there will most likely be another block that contains two 'F' values. If I'm looking for 'M' values, then the bitmap index will cause two reads of the block containing 'M' values and no reads of the block containing 'F' values. Still two blocks are read. While I may not be making myself clear, the bottom line is that for a column with such low cardinality, it is better off doing a full table scan than trying to use an index. This is even easier to see if the cardinality=1! (an extreme case in point). So while there is an upper "bound" where bitmap indexes become useful, there is also a lower "bound".

Thanks,
Brian

Gary Waldrom wrote:
>
> From experience you should only be looking at a bitmapped index if your
> cardinality ratio is lower that 1:20000
>
> i.e. if you have a table with people in it and you store a Gender flag
> of 'M' and 'F' you would need approx 40,000 rows before this becomes in
> any way efficient
>
> Gazza
>
> -----Original Message-----
> From: -=< a q u a b u b b l e >=-
> [mailto:aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com]
> Posted At: 23 November 1999 14:40
> Posted To: server
> Conversation: bitmap index
> Subject: Re: bitmap index
>
> Norris <jcheong_at_cooper.com.hk> wrote in message
> news:81dnrc$1mjc$1_at_adenine.netfront.net...
> > What is low cardinality?
>
> Not very many distinct values in a column for all of your rows.
Received on Wed Nov 24 1999 - 08:26:39 CST