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: Re bitmap index

Re: Re bitmap index

From: Gary Waldrom <garyw_at_amonra-insight.com>
Date: Fri, 26 Nov 1999 09:10:48 -0000
Message-ID: <E47927FC0A9CD3119D090000216DAFF5D09D@THOTH>


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

Original text of this message

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