Re: Bitmap Index

  • From: tboss@xxxxxxxxxxxxxxxxxx
  • To: oracle.tutorials@xxxxxxxxx
  • Date: Mon, 10 Oct 2005 09:39:47 -0400 (EDT)

In your case, i'd definitely say your data has low enough cardinality
to warrant a bitmap index.  15 distinct values over several million rows
is very low cardinality data, in the grand scheme of things.  A bitmap
index should work very well for you.  

As with most Oracle parameters/decisions ... there is not hard and fast rule
for what level of data cardinality makes sense.  The best answer is always
to do it both ways, test your application and see what is best.  Asktom
has an interesting thread titled "BITMAP index not being used" that has his
thoughts on the issue.  One salient point is (summarized):
- if you're doing lots of select count(*) from table where value='X' then 
a bitmap index is probably best
- if  you're doing lots of select * from table where value='X', then forcing
the use of an index when you'll be returning a high % of the table anyway
will be WORSE than just tablescanning in the first place.
- if your data is low cardinality but highly skewed (say, the gender values
of the freshman class at VMI, thousands of males, a handful of females),
then using a bitmap index to retrieve data is only good SOME of the time.

BTW In a warehouse, if you're trying to achieve star transformations, 
bitmap indexes are the way to go no matter what, since you're probably doing
counts instead of retrieving rows.  Hell, we're considering putting 
a bitmap index on the last name of a particular table (500k distinct values over
a data set of 13M rows) just to see if we can avoid a hash join and
force the star join.

my 2 cents, Todd

ps: VMI ancedote mentioned above explained; I live in Virginia, where the 
Virginia Military Institute (VMI) was forced in 1995 to start accepting female 
students or risk losing its state funding.  The school has about 1300 students,
but at most 50 females.

> ------=_Part_12426_3909019.1128939351052
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
> Content-Disposition: inline
> Hi All,
>  In my DWH DB I have a table with few million rows. I have a column in the
> table having 15 distinct values. That column is being used in the where
> clause of the queries. What I am interested to know is would it be
> beneficial to create a bitmap index on it.
> Is there any specific ratio (distincy values)/(Num of rows) at which one
> must consider creating bitmap indexes rather than b-tree indexes?

Other related posts: