peledc
asked on
Btree vs. Bitmap index
Hi
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
1,000
10,000
100,000
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?
Thanks
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
1,000
10,000
100,000
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?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
Peled