In a bitmap index, you have
a) a key value
b) a low-rowid value
c) a high-rowid value
d) a bit map that indicates for each rowid in that range whether the row that rowid points to has that value or not.
Suppose you have the EMP table and you create bitmap index bm on emp(deptno);
So, in the bitmap index, you might have key values like:
DEPTNO LOW-ROWID HIGH-ROWID BITMAP
---------- ------------ ------------- ---------------
10 afdafda badfasd 01010101010101
10 bbdafadafd bcdafdfda 11010101010101
20 afdafda badfasd 10101010101010
so, for each deptno in the EMP table, you will have 1 or more entries in the bitmap index. Each entry in the bitmap index points to potentially HUNDREDS of rows (the rows between low and high rowid).
Suppose you update:
update emp set deptno = 20 where empno = 1234;
and empno 1234 was in DEPTNO=10. That will update one of the bitmap keys bitmaps to remove the "1" (presume that is the first key in the above example). That will update one of the bitmap keys bitmaps to add a "1" for deptno=20 (presume that is the third key above).
Now, those two bitmap entries are locked by you.
Now, suppose I try to
update emp set deptno = 10 where empno = 5678;
And further, presume that record is currently showing deptno=20. That record is pointed to by the third bitmap key above - and when I try to update it, I block - I block on you - you have that key locked already. We are updating entirely different rows, however we need to update the SAME bitmap key since that bitmap key points to hundreds of rows.
... is this locking overhead related to the *fact* that bitmap indexes are not recommended for heavy DML stress tables ? ...
There is a locking implication by design in bitmap indexes, the keys point to hundreds of rows and if I lock a single key entry in a bitmap index, I've locked the key entry for hundreds of other rows.
that is WHY bitmaps are not recommended - if you update a bitmap indexed column OR you insert/delete from the table (which will modify the bitmap index), you'll find serialization at best and deadlocks all over the place at worst.