In case you do not know it yet, having a bitmap on columns like GENDER(male/female) is a very bad practice in OLTP, because each insert does lock the whole table
create table t(name varchar2(10), gender varchar2(10));
create bitmap index bi on t(gender);
+--------------------------------+ +--------------------------------+
| Session 1 | | Session 2 |
+--------------------------------+ +--------------------------------+
| SQL> insert into t | | |
| 2 values('JO','MALE'); | | |
| | | |
| 1 row created. | | |
| | | |
| | | SQL> insert into t |
| | | 2 values('JANE','FEMALE'); |
| | | |
| SQL> commit; | | |
| | | |
| Commit complete. | | |
| | | |
| | | |
| | | 1 row created. |
| | | |
| | | |
+--------------------------------+ +--------------------------------+
A pending transaction is blocking a portion of the index. So session 2 has to wait for transaction 1 to complete.
read comments on this post
If not the whole table then a large numbers of rows!
There is a limit to the number of rows referred to in each bitmap. multiple bitmaps for a key value do happen
I thought having a bitmap on columns like GENDER(male/female) is a very bad practice in general, regardless of locking issues.
William – what is “in general” 😉
Yes, bad if it is the only bitmap index on a table, but possibly good if there are others that can be ‘ANDed’ away with the gender bitmap to give a very small set of rows. That said though I have never bitmap indexed gender
Yes, that’s what I meant. The example didn’t mention any other bitmap indexes that could be used in combination, so I couldn’t see much use for that particular index. I appreciate that was just a contrived example and Laurent was making a point about locking though.
Please don’t propagate hoary old myths! A bitmap index does NOT cause “the whole table to be locked” when being updated.
Whereas a B*Tree index has individual entries for each row in a table so that updating a table row only causes us to lock one index entry, a bitmap index has a string of bits describing (in your example) the ‘maleness’ of all rows in the table and a different string of bits describing the ‘femaleness’ of all rows. When you update the index (because you’ve updated the table) you take a lock on the string of bits -technically known as the bitmap segment. Importantly, however, the string of bits has to be broken up so that it fits into different Oracle blocks… and an update to one block’s-worth of the string of bits doesn’t cause the other blocks’ contents to be locked.
If you consider that an 8K block can store about 65,000 bits; and if you work with the rough assumption that one bit describes the maleness of rows and one bit describes femaleness, then an 8K block can store a bitmap segment for males/females that covers about 30,000 rows in the table. When you update one row in the table, you find the bit in a bitmap segment that references that row, and you lock that entire bitmap segment in order to update that one bit. That has the effect of locking the approximately 30,000 rows in the table also covered by that bitmap segment. But it doesn’t lock the Lord knows how many other rows that are not covered by that bitmap segment.
Now, that’s extremely bad news if you are an OLTP environment and need highly concurrent DML taking place on that table, but it’s definitely not the entire table being locked, and if your table had 50 billion rows in it, you might well not notice 30,000 of them being locked for one update!
thanks Peter and Howard for making this point clear, I really appreciate
“then an 8K block can store a bitmap segment for males/females that covers about 30,000 rows in the table”
You’ve missed the concept of compression here. Broadly, oracle doesn’t always store one bit per database row. It can store something reflecting “the 100,000 rows between rowids X and Y are all flagged zero” As such, a single bitmap segment can cover many more than 30,000 rows. It COULD cover an entire table.
Check page/slide 24 onwards in
http://julian.dyke.users.btopenworld.com/com/Presentations/BitmapIndexInternals.ppt
According to the Wikipedia entry, there appears to be a lot of new research work that ORACLE has not consider. Does anyone have any interest in pushing ORACLE to do something in that front?
For those who are interested in getting their hand dirty, I have a free software package called FastBit that implements a set of very efficient compressed bitmap indexes. The software is available for download at http://sdm.lbl.gov/fastbit/src/. You can also find lots of information about the algorithms behind the software at http://sdm.lbl.gov/fastbit/.
Pingback: Book Review: Oracle Database 11gR2 Performance Tuning Cookbook (Part 1) « Charles Hooper's Oracle Notes
Pingback: OLTP sistemlerde Bitmap index kullanımının sakıncaları | eneskoroglu