Re: Tx lock problem with bitmap index

Yikes!

If there is ONE thing you should know about bitmap indexes, it is that they do not mix with DML. Ever. Bitmap indexes are brilliant in their place, but a table where DML is taking place on the indexed column is not one of them.

The trouble is that a leaf node for a bitmap index contains an entire bitmap segment for the key value. For example, if you bitmap-index the 'CITY' column, you'd have a leaf node containing a bitmap for 'SYDNEY', another leaf node for 'MELBOURNE' and a third for 'ADELAIDE'.

For a twelve-row table...

Leaf node 1 might look like: 001111001010
Leaf node 2 might look like: 100000110001
Leaf node 3 might look like: 010000000100

Each run of bits counts as a separate bitmap segment, and (here's the killer) when you update the city column, the *entire* bitmap segment gets locked. Which in this case means that a single update (or insert, delete) has effectively just locked 12 rows of the table.

Given an 8192-byte block, it's possible to store getting on for 40000+ bits in each leaf node. Meaning that a single piece of DML might end up locking 40,000+ rows of the table.

B*Tree indexes don't have this problem, since each leaf node entry points to one row of the table, and the updating of a row therefore only needs to lock that one leaf node entry.

Moral of the story: don't stick bitmap indexes on columns which are going to be subject to any significant degree of DML unless you are prepared to live with the dreadful concurrency issues that will ensue.

Regards
HJR "Saikat Chakraborty" <saikatchak_at_hotmail.com> wrote in message news:1f619969025c2aa41c6f1e8ef513c8b6.16981_at_mygate.mailgate.org...
> Hi,
> I am facing a possible oracle limitation in Oracle 8.1.6 (Solaris 7) in
> bitmap index. Unfortunately upgrade is not an option. The problem is as
> follows:
>
> create table atest
> (
> a varchar2(10),
> b number(10)
> );
> I created a bitmap index on the column a. No Primary key or any
> constraint is present on the table. Now from two separate sessions,
> I issue the same insert statement:
>
> insert into atest values('A',10);
>
> If the first session does not commits, the second insert gets locked.
> Tracing shows only the insert is locked. Looks like the Bitmap index can
> not work with concurrent two inserts. But does not inserts should not be
> locked?
> Recreating the indexes as B-tree index makes the problem go away.
> Any insights will be helpful.
>
> Thanks and Regards,
>
> Saikat Chakraborty
>
>
> --
> Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Wed Dec 18 2002 - 13:50:43 CST