Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Re bitmap index
Very simple example coming up
If we are looking at a table of people and we wish to find all people
with blonde hair and blue eyes, a bitmap index on hair_colour and
another on eye_colour will give two bitmaps as such
idx_hair_bm
Person Brown Black Blonde Grey Red John 1 0 0 0 0 Sue 0 0 1 0 0 Mark 0 0 0 1 0 idx_eyes_bm Person Green Blue Brown John 0 0 1 Sue 0 1 0 Mark 0 1 0
A simple AND of the two indexes looking for blonde hair and blue eyes will give
Hair Eyes Result John 0 0 0 Sue 1 1 1 Mark 0 1 0
Therefore Sue meets the criteria
That's the theory
Gary Waldrom
-----Original Message-----
From: minkuarora_at_yahoo.com [mailto:minkuarora_at_yahoo.com]
Posted At: 25 November 1999 08:33
Posted To: server
Conversation: bitmap index
Subject: Re: Re bitmap index
Hi !
I did go through the whole bunch of explaination which u have given about bitmap index ..i did understood quite a bit of it ..but i have a very fundamental question as to what exactly is bitmap index and how do we use it in a SQL querry could u please explain this with a example
Thanks & Regards
Parvinder
In article <383BF59F.CAB68D88_at_edcmail.cr.usgs.gov>,
Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
> There is a problem with trying to use bitmap indexes on columns that
> have too low of a cardinality. If the cardinality is too low, it is
> better to do a full table scan. The "Gender" flag is a perfect example
> of a case where I normally wouldn't use a bitmap index. The reason for
> this is distribution and spread.
>
> If I have a Gender flag, it is normally safe to assume that there will
> be approx 50% Male and 50% Female. Further assume that the data is
> evenly distributed throughout the table. If a db_block in my table
holds
> two rows, one row will mostly likely contain 'M' while the other row
> most likely contains 'F'. I still have to read this entire block to
get
> the data I'm looking for. If the block contains two rows with both 'M'
> values, then there will most likely be another block that contains two
> 'F' values. If I'm looking for 'M' values, then the bitmap index will
> cause two reads of the block containing 'M' values and no reads of the
> block containing 'F' values. Still two blocks are read.
> While I may not be making myself clear, the bottom line is that for a
> column with such low cardinality, it is better off doing a full table
> scan than trying to use an index. This is even easier to see if the
> cardinality=1! (an extreme case in point). So while there is an upper
> "bound" where bitmap indexes become useful, there is also a lower
> "bound".
>
> Thanks,
> Brian
>
> Gary Waldrom wrote:
> >
> > From experience you should only be looking at a bitmapped index if
your
> > cardinality ratio is lower that 1:20000
> >
> > i.e. if you have a table with people in it and you store a Gender
flag
> > of 'M' and 'F' you would need approx 40,000 rows before this
becomes in
> > any way efficient
> >
> > Gazza
> >
> > -----Original Message-----
> > From: -=< a q u a b u b b l e >=-
> > [mailto:aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com]
> > Posted At: 23 November 1999 14:40
> > Posted To: server
> > Conversation: bitmap index
> > Subject: Re: bitmap index
> >
> > Norris <jcheong_at_cooper.com.hk> wrote in message
> > news:81dnrc$1mjc$1_at_adenine.netfront.net...
> > > What is low cardinality?
> >
> > Not very many distinct values in a column for all of your rows.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Nov 26 1999 - 03:10:48 CST