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
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...
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.
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
"Some days you're the pigeon, some days you're the statue..."
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...
> "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.
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
Best regards,
Jim Davis
"Connor McDonald" <connor_...@yahoo.com> wrote in message
news:3BB0C7...@yahoo.com...
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...
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>...