Btree vs. Bitmap index


On Oracle 10g

I'm running some benchmark tests regarding the differences between the two types of indexes: Btree and Bitmap.
I have created a table with 15,000,000 rows that occupies around 2 Gigabyte.
I have create 5 different indexes with the following number of distinct keys:
2 keys
4 keys
15,000,000 distinct keys.

1. I have created these indexes once in Btree mode and once in Bitmap mode.
2. I executed around 5,000 queries, 1000 updates and delete against each index in each mode.
3. I have execute a 20 end users concurrent updates against the 100,000 keys index

There are very minor changes in performance.
The only main difference is the size of the index. The Btree is much smaller: between 4 to 40 times smaller than the Btree index.

Now, my question is, Have I forgot any tests here? Did you ever get different results? Is there a hidden problem with the Bitmap indexes that would make them unsuitable for OLTP environment?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
Yes, bit-mapped indexes are usually much smaller than btree indexes.  With your data volumns I would expect them to be much better for the two columns that have only 2 or 4 distinct values.  For the columns with 1,000 or more distinct values, they may not be better.

"Is there a hidden problem with the Bitmap indexes that would make them unsuitable for OLTP environment?"

There can be a huge problem with contention (locking) of bit-mapped indexes in  OLTP environments.  They are best suited to data warehouses only.  The problem in OLTP environments is the number of records that are affected in the index when one value is changed.  For example, in your situation with the column that has only 2 distinct values, if this value is being updated, the part of the index that corresponded to its old value is locked (which may effectively lock 50% of the rows in the table).  If someone else is trying to update another of those records (even if it is a different column) you may see long waits for locks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peledcAuthor Commented:
Thanks Mark.

I have tested my updates on the 100,000 keys index and therefor did not encounter this problem.
I have just tried your suggestion and got the long waiting times.

I will email you my White paper when its done.

Have a lovely day
Well, I will bring back some of my reading memories and few experiences here:

Btree indexes are ideal for OLTP transactions, why is that? because og UPDATES behavior. If you want to save some space with btree indexes try reducing the PCTFREE parameter (careful).
These indexes are designed for queries that will bring a small result of rows, for example, if you query a 15milllion table and expect 7 million (or even 1million) of rows oracle do not recomend to index the field. On the other hand if you query that table and expect only 100000 (or less) records (according to oracle) the index will be well used.

Bitmap Indexes are much smaller, in fact, the size og the BITMAP indexes vary depending the cardinality of the field.
For example, if in your 15million rows the indexed field have only 2 different values the index will be smaller than if there are 10 different values.
So, bitmap indexes are designed for datawarehouse applications where indexed field have low cardinality and will be used for reporting and will expect as m uch rows as needed.

Bitmap indexes tend to have a high locking behavior in OLTP environment, so, again, use only for datawarehousing/reporting databases.

hope it helps.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.