Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tx lock problem with bitmap index

Re: Tx lock problem with bitmap index

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 18 Dec 2002 14:03:51 -0800
Message-ID: <2687bb95.0212181403.8b3294b@posting.google.com>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US