Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Bitmap Idx
You missed the point of what I was saying, in fact u missed the point of the orginal message which was why after creating a bit map oracle didnt use it.
Also from the Oracle manual:
http://www.oradoc.com/ora816/server.816/a76965/c20a_opt.htm
Histograms are not useful for columns with the following characteristics:
All predicates on the column use bind variables.
The column data is uniformly distributed.
The column is not used in WHERE clauses of queries.
The column is unique and is used only with equality predicates.
NOTE: that they are not useful if :
The column data is uniformly distributed.
which is exaclty the point i was trying to make! if the data is evenly distributed the cardinality doesn't matter much low or high or whatever. Oracle will assume that a full table scan is the best option.
And as i noted if the column is not evenly distrubuted then he needs to use a historgram to make sure Oracle knows that and uses it appropriately. However, even with that it may determine that a full table scan is the best.
So just because a column has low cardinality doesnt mean its the best candiate for a bit map. its distribution over a table is also critical
also how its used with other columns with or without indexes. For example having a bitmap on a gender column and a date colum a query that say give me all males for the lst 2 years will probalby use a full scan but a query asking for all males in x month will use bitmap.
In article <RPcW6.27760$3y3.4748939_at_typhoon.nyroc.rr.com>, "John Morais"
<jmorais_at_rochester.rr.com> wrote:
>I beg to differ Mel ... Bitmap indexes are especially useful if you have a
>low cardinality.. I have attached part of an Oracle document in this message
>when to use bitmapped indexes :
>
>3) When to use bitmapped Indexes
> -----------------------------
>
>- the column has a low cardinality: few distinct value
>
>- bitmapped indexes are especially helpful for complex ad hoc queries with
> lengthy WHERE clauses or aggregate queries (containing SUM, COUNT, or
>other
> aggregate functions)
>
>- the table has many rows (with 1.000.000 rows is 10.000 distinct values
> possibly acceptable)
>
>- there are frequent, possibly ad hoc, queries on the table
>
>- the environment is data warehouse-oriented (DSS system). Bitmap indexes
> are not ideal for online transaction processing (OLTP) environments
> because of their locking behavior. It is not possible to lock a
> single bitmap position.
>
> The smallest amount of a bitmap that can be locked is a bitmap
> segment, which can be up to half a data block in size. Changing the
> value of a row results in a bitmap segment becoming locked, in effect
> blocking changes on a number of rows.
>
> This is a serious disadvantage when there are many UPDATE, INSERT
> or DELETE statements being issued by users. It is not a problem when
> data is loaded or updated in bulk actions, as in data warehouse
> systems. This because the index needs to be rebuilt for every update,
>inser or delete.. AND I MEAN REBUILT..
>
>"mel" <jmel_at_mailnews.com> wrote in message
>news:3b2933cb$0$270$45beb828_at_newscene.com...
>>
>> 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 Fri Jun 15 2001 - 10:06:03 CDT