Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Bitmap Idx
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 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 Thu Jun 14 2001 - 19:40:17 CDT