Re: When to use bitmap indexes

That's actually a bit (no pun intended) over the top. The bitmap for each column value is broken down into sections which are a maximum of (I think) one-third of a block. In a 16K block, this means that a single entry could be over 5KB, or around 45,000 bits

Allowing for overheads and compression, you can safely assume that any one index entry can cover 10's of thousands of rows.

When you update a single table row to change the value of the indexed column, you have to lock two index entries (the FROM and the TO). Unless you do big tests this can make it look as if you are locking the whole bitmap - or even the whole table. Nevertheless, it's extremely bad news, and shouldn't be done.

Just to put the icing on the cake, the bitmaps entries that are updated often end up being split into two sections each - which have to be written back into the index without overwriting the original entries - which is why bitmap indexes also happen to expand so rapidly when you change the data.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Niall Litchfield wrote in message <9oqm8n$3a5$1_at_uranium.btinternet.com>...


>In principle it works like this

>

>For each and every piece of DML the entire bitmap has to be recalculated.

>Ergo if you have an OLTP type situation the overhead on nomal transactions

>is far far too high. For a data warehouse of course you drop the index,

load


>and recreate the index.

>

>HTH

>

>Niall

>"Jim Davis" <jimdavis_at_iprolink.ch> wrote in message

>news:3bb00528$1_at_news.swissonline.ch...

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

>>

>>

>

>