Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bitmap Idx

Re: Bitmap Idx

From: John Morais <jmorais_at_rochester.rr.com>
Date: Fri, 15 Jun 2001 00:40:17 GMT
Message-ID: <RPcW6.27760$3y3.4748939@typhoon.nyroc.rr.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US