Btree vs. Bitmap index

Posted on 2004-11-24
Last Modified: 2013-12-03

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?

Question by:peledc
    LVL 34

    Accepted Solution

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

    Author Comment

    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
    LVL 1

    Expert Comment

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to recover a database from a user managed backup

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now