Re: When to use bitmap indexes

From: Jim Davis <jimdavis_at_iprolink.ch>
Date: Tue, 25 Sep 2001 21:20:13 +0200
Message-ID: <3bb0da17@news.swissonline.ch>

Thanks, guys. I keep hearing things like this, which is why I haven't yet taken the bitmap plunge. We have several cases where the cardinality points towards a bitmap index, but in tables that are constant updates from users all over the world.

Best regards,

Jim Davis

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3BB0C785.1CC5_at_yahoo.com...
> Jim Davis wrote:
> >
> > Connor,
> >
> > As one having been contemplating implementing bitmap indexes for a few
> > cases, my curious mind wonders if you would be willing to expound on:
> >
> > "NB: Bitmaps and changes to the tables do NOT mix at all well"
> >
> > Thanks in advance,
> >
> > Jim Davis
> >
> > "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> > news:3BABBFEA.6E8D_at_yahoo.com...
> > > Michiel Brunt wrote:
> > > >
> > > > In our data warehouse we have a large fact table with keys to
dimension
> > > > tables.
> > > > Something like:
> > > >
> > > > Product_key Customer_key Time_key Revenue
> > > > 1 1 1 1000
> > > > 1 1 2 2000
> > > > 1 2 2 1000
> > > > 1 3 1 1000
> > > > 1 3 2 2000
> > > > 2 1 1 500
> > > > 2 1 2 1000
> > > > 2 2 2 2000
> > > >
> > > > This fact table contains 10 mln records.
> > > > There are 10.000 products and 500.000 customers
> > > > The question is wether the use of bitmap indexes is useful.
> > > >
> > > > I heard that the number of distinct values in a column should be
less
> > than
> > > > 10% of the total number of rows in the table.
> > > > In that case 10.000 / 10.000.000 is fine and even 500.000 /
10.000.000
> > would
> > > > work.
> > > >
> > > > Thanks,
> > > > Michiel
> > >
> > > One of the great things with bitmaps is that they take very little
time
> > > to create, and very little space - so I'd just bung them on and see
how
> > > you go...
> > >
> > > NB: Bitmaps and changes to the tables do NOT mix at all well
> > > --
> > > ==============================
> > > Connor McDonald
> > >
> > > http://www.oracledba.co.uk
> > >
> > > "Some days you're the pigeon, some days you're the statue..."

> When you change entry in a table that has a bitmap index on it, you can
> quite easily lock most of the table. The bitmap for that entry is
> basically locked whilst you do the changes - which of course overrides
> the row level locking concept.

> Try it yourself - create a table with a GENDER column (M or F), smack in
> a couple of thousand rows and then update just 1 of them from M to F
> (without committing).

> See how much of the rest of the table you can touch ... probably none.

> Similarly, a feature of bitmaps is that they are so small... But when
> you update them on the fly, they tend to grow FAST...

> Don't get wrong - in the right environment - bitmaps are awesome.

> hth
> connor

> --
> ==============================
> Connor McDonald

> http://www.oracledba.co.uk

>
> "Some days you're the pigeon, some days you're the statue..."
Received on Tue Sep 25 2001 - 14:20:13 CDT