Re: When to use bitmap indexes

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..."