Re: Locks on Bitmap Indexes

sv wrote:
>
> Hi,
>
> I´m a developer of a database aplication on oracle 8. In several tables I
> use bitmap indexes to increase performance.
> To prevent users from waiting on each other I check rows being updated if
> they are locked by another user. For higher level of locking I use the
> dbms_lock package. Everything works fine so far. I can prevent most
> conflicts with row locks.
> My problem know is that some tables use bitmap indexes. If I update any
> bitmapped indexed column in a table oracle locks the index-key and I cannot
> make inserts on that table with the same key-value until the user who
> updates make a commit. As I know this is because of the bitmapped index. My
> question now is:
>
> Is there a way to check if a key-value of a specific bitmap index is locked
> by another user ?
> Does anybody know a package or a V$ ??? table who gives information about
> that ?
>
> Thanx
>
> Stephan Voigt

Hi Stephan.

Be careful, its dangerous to update bitmapped idexed columns !!

Look at this NEWS I read today :
I would like to remind you that Oracle's documentation recommends using bitmapindexes ONLY for DSS and NEVER for OLTP.

In your example, (Niemann) you have 72 distinct values out of 750,000+ rows,
sounds perfect for a bitmapped index, not so when DML are performed on the
underlying table.
The reason why is simple, the internal layout of a bitmapped index will only have
72 "rows" as
compared the a regular B-tree index which will hold a "row" in the index for each
value in underlying table (750,000).
Duplicate rows in bitmap index are bundled under same "row" in index, whereas they
get separate "rows" in
regular B-tree index.
Recall Oracle's row-level locking, hence when you INSERT (or any other DML) a
value into a table, the corresponding "row" in the bitmap index is locked, thus preventing any DML's on that value, until that lock
is released (commit, or rollback).
If you had a bitmap index on sex column (50% M, 50% F), by inserting one row
(without committing) you are now locking 1/2 of the table. Such condition will not hold true with a regular B-tree index, since when you
insert a new row into the table you are also creating a new "row" in the B-tree index (in the bitmap similar values are bundled under same "row"),
so there are no locking issues with a regular B-tree index.

If you do have a bitmap index and DML going at the same time, you will see
increase in enqueue waits, specifically TX enqueue.

I think the concepts manual talks about it in more details. bottom line, bitmap
index is good for DSS (no/little DML's) and very bad for OLTP (lots of DML's).

Saar. Received on Fri Nov 20 1998 - 09:06:12 CST