When to use bitmap indexes

0 views
Skip to first unread message

Michiel Brunt

unread,
Sep 21, 2001, 4:20:56 AM9/21/01
to
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


MT

unread,
Sep 21, 2001, 8:50:03 AM9/21/01
to
Hello Michiel:

You should be OK although you should first experiment. My experience
with BMIs is very good. I have a table with over 40 million
records and 250.000 products. The BMI give much better performance
than the B*Tree indexes.

Make sure you create histograms on the critical columns and
use the INDEX_COMBINE hint to make it clear to the optimizer
what path it should take.

Once you have created the BMIs you should also test your loading
procedures because we found that the indexes would in some
cases cause a slowdown of inserts with respect to normal B*Tree
indexes.

Good luck,
Mario


"Michiel Brunt" <mbr...@inergy.nl> wrote in message news:9oet4l$252u$1...@scavenger.euro.net...

Dusan Bolek

unread,
Sep 21, 2001, 11:28:38 AM9/21/01
to
"Michiel Brunt" <mbr...@inergy.nl> wrote in message news:<9oet4l$252u$1...@scavenger.euro.net>...

My opinion is that product_key is perfect example for bitmap index.
However with customers I'm not sure. You can try, but I would prefer
not using bitmap index for this column.

--
_________________________________________

Dusan Bolek, Ing.
Oracle team leader

Note: pages...@usa.net has been cancelled due to changes (maybe we
can call it an overture to bankruptcy) on that server. I'm still using
this email to prevent SPAM. Maybe one day I will change it and have a
proper mail even for news, but right now I can be reached by this
email.

Connor McDonald

unread,
Sep 21, 2001, 6:32:10 PM9/21/01
to Michiel Brunt

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

Jim Davis

unread,
Sep 22, 2001, 7:56:13 AM9/22/01
to
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_...@yahoo.com> wrote in message
news:3BABBF...@yahoo.com...

Galen Boyer

unread,
Sep 25, 2001, 1:10:10 PM9/25/01
to
On Sat, 22 Sep 2001, jimd...@iprolink.ch wrote:

> "NB: Bitmaps and changes to the tables do NOT mix at all well"

He's saying that when columns that have indexes on them get
updated, the indexes get updated as well. Updates to bitmap
indexes are _very_ expensive. That's why "do NOT mix ..." In my
experience, bitmap indexes are used for query only tables, ie,
reporting tables.

Here is online documentation on bitmap indexes for 8.1.7
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c08schem.htm#5381

--
Galen Boyer
Everyone in town, now, they probably all agree,
I'm _lying_ in the bed I made.

Connor McDonald

unread,
Sep 25, 2001, 2:05:58 PM9/25/01
to Jim Davis

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

Jim Davis

unread,
Sep 25, 2001, 3:20:13 PM9/25/01
to
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_...@yahoo.com> wrote in message

news:3BB0C7...@yahoo.com...

Niall Litchfield

unread,
Sep 25, 2001, 3:41:35 PM9/25/01
to
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" <jimd...@iprolink.ch> wrote in message
news:3bb00528$1...@news.swissonline.ch...

Jonathan Lewis

unread,
Sep 26, 2001, 4:17:51 PM9/26/01
to

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...@uranium.btinternet.com>...

Reply all
Reply to author
Forward
0 new messages