Re: indexing a column with only 2 or 3 values

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

ciapecki wrote:
> On 3 Aug., 09:54, Thomas Kellerer <JUAXQOSZF..._at_spammotel.com> wrote:

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

>
> Thanks Thomas,
>
> Is there any rule when to use BMap and when BTree?
> I know for a few values BMap, for many different ones BTree, but maybe
> there is some mathematical equation that helps to determine which
> Index is better.

Apart from comparing execution plans, I suggest to read the relevant chapter in the concepts manual:

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref1008

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

Thomas Received on Fri Aug 03 2007 - 03:03:56 CDT