[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1755
  • Last Modified:

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?

1 Solution
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.
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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now