Re: Bitmap Idx

Oracle determines usage depending on various factors.

1 oracle assumes that the values for a bit map are evenly distributed over the table.

2 If the cardinaltity is very low and since it assumes that it is evenly spread then it will assume that a full table scan if best. The reason being that if for example a column has 3 values a, b,c and they are evely spread over tha table i.e. every 3rd column is an a, then doing a full table scan is MUCH faster than reading the index and going to every 3rd row,

3 a hint will force the use even if it is not the best.

so if you have a low cardinality column that is evely spread over the table, oracle will not use the index.

what if it is low cardinality and not evenly spread. Well oracle needs to know this. You have to do an analyze WITH histograms so that oracle can develop a detailed knowledge of the distriubution.

in that case let us assume that out of 1 miilion rows a occurs 500k, b 490k and c 10k

then when u query oracle will use a bit map for c but a full table can for a and b, because a full scan is cheaper than looking at the index and then a row.

so do an analyze with historgrams and then let oracle decide on the plan

In article <II2W6.27474$3y3.4364794_at_typhoon.nyroc.rr.com>, "John Morais" <jmorais_at_rochester.rr.com> wrote:
>If your are running CBO first thing is analyze the table.
>
>"Sunder" <sunder.nochilur_at_us.bosch.com> wrote in message
>news:3B28B11D.AD6B2A6_at_us.bosch.com...
>> I have a table called TABLEA with columns COL1, COL2, COL3, COL4
>> I have created Bitmap indexes on COL1 and COL2.
>>
>> When I do a explain plan on
>>
>> select count(!) from TABLEA where COL1 = 'A';
>>
>> The indexes are not used.
>>
>> If i do an explain plan on
>>
>> select /*+ INDEX(TABLEA,COL1_IDX) */
>> count(1) from TABLEA where COL1 = 'A';
>>
>> The index is used.
>>
>> My question : how do I get the bitmap index to be used by default.
>> (without using the hint ).
>>
>> Thanks
>> Sunder
>>
>
>
Received on Thu Jun 14 2001 - 17:02:02 CDT