Re: Re bitmap index

Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:

> 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".

        This bad performace of bitmap indexes can be avoided with use of histograms. With histograms, the optimizer will that near 50% of the table would be acessed, and it will choose a full scan.

        In other way, if a order table has a status column with three possible values ('C'ompleted, 'H'old, 'F'ailed), and 99% of the values are 'C', the full scan will be used just with queries with the 'C' values, the others will use the bitmap index.

--
Atenciosamente
Luis Santos Received on Wed Nov 24 1999 - 19:55:18 CST