Re: indexing a column with only 2 or 3 values

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Fri, 03 Aug 2007 13:01:03 GMT
Message-ID: <jGFsi.15597$4A1.1077@news-server.bigpond.net.au>

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

Cheers

Richard Received on Fri Aug 03 2007 - 08:01:03 CDT