Re: Bitmap Index

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?
>

--
http://www.freelists.org/webpage/oracle-l