Re: indexing a column with only 2 or 3 values

From: Thomas Kellerer <JUAXQOSZFGQQ_at_spammotel.com>
Date: Fri, 03 Aug 2007 15:10:50 +0200
Message-ID: <5hgnqqF3l1n69U1@mid.individual.net>

Richard Foote wrote:
> "Thomas Kellerer" <JUAXQOSZFGQQ_at_spammotel.com> wrote in message
> news:5hg5aiF3kia4nU1_at_mid.individual.net...

>> ciapecki wrote:
>>> Hi,
>>>
>>> Does indexing a very big table (about 5Mio records) on the columnA
>>> which can hold only values Y,N,<NULL> make sense?
>>>
>> Yes, that's what bitmap indexes were made for.
>>

>
> Hi Thomas
>
> A Bitmap index is of no use if both Y and N are roughly evenly distributed
> and you have no other predicate in the query.
>
> Returning approximately 2.5 millions rows through a bitmap index would be
> dramatically slower than a full table scan.
>
> A single bitmap index only would be useless in this scenario, even more so
> if the table is subjected to any transactional based DML load.
>

Hi Richard,

thanks for the pointing this out.
I wasn't aware of that, but it does sound reasonable.

But after all the Concepts manual says:

"If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index."

Actually a bit further down in the Concepts manual there is an example very similar to the OP's situation:

"There are only three possible values for marital status and region, two possible values for gender, and four for income level. Therefore, it is appropriate to create bitmap indexes on these columns"

Cheers
Thomas Received on Fri Aug 03 2007 - 08:10:50 CDT