In the case explained here ( a query on a table with
only one condition on the gender column), a bitmap
index will not help as the optimiser will do a full
table scan.
Bitmap indexes are good when there are many of them,
then the optimiser merge them (typically : the foreign
keys of a fact table in a DSS system).
An histogram is a structure used when the data has not
a regular distribution, it helps the optimiser in
deciding for the best access path. The restriction
with histograms is that it can not be used with bind
variables.
HTH
- Abdul Aleem <abchaudhary-ho_at_beaconhouse.edu.pk> a
écrit : > Wait...Wait.... Wait....
>
> Do I get it correct? To my understanding BITMAP and
> BTREE are entirely the
> different types of indexes. BTREE being the default
> of Oracle unless you
> specify BITMAP keyword. For a gender column, I think
> Bitmap is the most
> suitable type of index. I am sorry that Histogram
> are new to me, if you
> could possibly shed some light on it.
>
> Refer to the following extract form Oracle Commands
> Manual
>
> Creating Bitmap Indexes
>
> Bitmap indexes store the ROWIDs associated with a
> key value as a bitmap.
> Each bit in the bitmap corresponds to a possible
> ROWID, and if the bit is
> set, it means that the row with the corresponding
> ROWID contains the key
> value. The internal representation of bitmaps is
> best suited for
> applications with low levels of concurrent
> transactions, such as data
> warehousing.
> See Oracle8 Concepts and Oracle8 Tuning for more
> information about using
> bitmap indexes.
>
> Regards,
>
> Aleem
>
>
> -----Original Message-----
> From: paquette stephane
> [mailto:stephane_paquette_at_yahoo.com]
> Sent: 30 May, 2000 3:52 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Histogram VS bitmap index
>
> Hi Luc,
>
> In applications I've worked on, gender was always
> near
> a 50/50 distribution, in that case an index (btree
> or
> bitmap) or an histogram are not useful.
>
> The case where an index can help on this kind of
> data
> is when you have a value with very few occurences
> and
> your queries are looking for that value most of
> the
> time.
>
> For example if the data has the following
> distribution
> A 35%
> B 1%
> C 30%
> D 34%
>
> If your queries are looking for value B then an
> index
> on the column can help.
>
>
>
>
> --- Luc Demanche <ldemanch_at_cetelem.fr> a écrit : >
> Hello,
> >
> > I have an EMP table with a field named GENDER.
> The
> > only two
> > possibilities
> > for this field is M or F.
> >
> > What is the best strategy for the cost optimizer
> > between:
> >
> > 1- Create a column histogram on GENDER
> > 2- Create a bitmap index on GENDER
> >
> > We don't use bind variables for GENDER
> (important
> > for histogram).
> >
> > Thanks in advance.
> >
> > --
>
> --
> Author: Abdul Aleem
> INET: abchaudhary-ho_at_beaconhouse.edu.pk
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50
Received on Tue May 30 2000 - 06:57:45 CDT
