Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tx lock problem with bitmap index
"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
Saikat, when you update a row in a table you also lock the associated index entries. I believe that one bitmap index entry can cover up to around 26000 table entries so this in effect locks all the associated rows. Bitmap indexes were introduced by Oracle for use in data warehouse applications where updates will not be taking place except in batch. Bitmap indexes are normally dropped during warehouse load processing and rebuilt after the data is loaded. Bitmap indexes are not recommended for use in OLTP applications.
HTH -- Mark D Powell -- Received on Wed Dec 18 2002 - 16:03:51 CST