Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Itsik.

Asked: December 10, 2007 - 7:08 pm UTC

Answered by: Tom Kyte - Last updated: December 14, 2012 - 2:31 pm UTC

Category: Database - Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom

I would like to ask a Question about Bitmap indexes, somethings in this indexes is still unclear for me.
Many rumors revolves around what exactly gets locked (and how) during a DML operation on column\row with Bitmap index, it starts with a full exclusive lock on the table (untrue) and ends with same behaver as Btree works (row-level exclusive lock of the effected rows), so what really happens in there, what is the logic ?
is this locking overhead related to the *fact* that bitmap indexes are not recommended for heavy DML stress tables ?


Thanks for your help
Itsik

and we said...


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.

and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

December 11, 2007 - 2:54 pm UTC

Reviewer: Itsik Harel

Thanks for your perfect answer.
Itsik

What about direct-path inserts?

December 11, 2007 - 6:27 pm UTC

Reviewer: PB

You said: "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."

What about doing insert /*+ append */ operations? I'm thinking in terms of data warehouse operations (single large, parallel insert in batch mode). Do those create the same serialization? I was thinking they wouldn't since index maintenance happens after the insert - is that right?

Tom Kyte

Followup  

December 11, 2007 - 9:44 pm UTC

well, given that insert /*+ append */ itself SERIALIZES (only one thing at a time will be doing that) there is by definition - no problem.

if you do a parallel insert /*+ append */ - no problem either - because each parallel thread will have it's own rowid ranges - their low/highs are different.


index maintain happens during the insert really - when you direct path insert, a mini index is built for the new data and merged in bulk to the main index after the work is done.

queries locks with bitmap indexes

November 25, 2008 - 5:38 am UTC

Reviewer: Avishai from Israel

Hi

Are there any locking issues with selects ( without the "for update" clause) during the update/insert operations when bitmap indexes are used? Or the good old "we don't block selects" / read consistency is true with bitmap indexes as well.

Thanks,
Avishai


Tom Kyte

Followup  

November 25, 2008 - 11:48 am UTC

good old read consistency and multiversioning - the read only selects will not block.


Bitmap index causing deadlocks.

November 22, 2012 - 4:29 am UTC

Reviewer: Sahoo Dillip from Bangalore, India

Hi Tom,
I am facing a situation where a materialized view used for reporting is being updated parallely by multiple jobs issued by a auto job scheduling tool. This materialized view is having multiple Bitmap Indexes.
The jobs updating Materialized views carryout either of the below statements:
(1) Merge followed by Delete
(or)
(2) Delete followed by Insert
Since 2-3 months post 10gR2 to 11gR2 migration , I am facing some of these jobs are failing due to Deadlocks. But same situation not used to exists with 10gR2.
I could see the tracefile signaling the deadlock was created due to blocks on Indexes and queries causing these deadlocks are mostly due to Delete combined with parallely running Merge.
Could you please guide us on suggestions/fix? Is that Deferring Indexes before DML operation then Activating Indexes using "ALTER INDEX <> IMMEDIATE" will help?
Tom Kyte

Followup  

November 29, 2012 - 6:33 am UTC

you cannot have a bitmap index on a table with multiple concurrent modifications. It just doesn't work (by design)

bitmap indexes are only suitable on a segment that is modified by one thing at a time. period.

You got lucky in 10gr2, nothing more, nothing less.


You'll have to forgo the bitmap indexes on any segment that undergoes concurrent modifications (this has *always* been true) or set them unusable, do your operation and rebuild them.

Materialized Views updates creating Deadlocks

December 10, 2012 - 4:31 am UTC

Reviewer: Dillip Kumar Sahoo from Bangalore, India

Thanks a ton for writing a followup on my query.

1) Is that switching index type from BITMAP to BTREE would help.

2) Please note, Business users are running very heavy queries on these MViews, so How badly the query performance would be affected due to change in index type from Bitmap to BTree.

3) Could you please clarify, we generally use BITMAP index on columns with less number of unique values. Is there any limits on number of unique/distinct values to be maintained by a BITMAP index.
As, The few columns on which BITMAP indexes are created have almost 10k to 80k distinct values.
Tom Kyte

Followup  

December 14, 2012 - 2:31 pm UTC

1) you cannot have bitmap indexes in an environment where you want more than one transaction happening at a time.

2) insufficient data. Unless you are merging more than one bitmap together to get your results, problem none. If you are merging many bitmaps together - probably a lot.

3) that isn't a reason to use bitmaps. that is a myth.
http://jonathanlewis.wordpress.com/2006/11/29/bitmap-indexes/


you use bitmaps predominantly when you want to merge together many of them at run time and there is no sensible way to b*tree index them. eg: an ad-hoc query environment where the users might pick any N columns out of M and query on them - no way to b*tree index them - but you could create a series of single column bitmaps that we'll merge together using AND/OR's to create a new bitmap index on the fly to find your rows.