Re: BITMAP JOIN INDEXes on highly transactional tables: dangerous ?

Hi, Spendius!

    Yes, very likely you will... Recently we had an issue, connected with bitmap joins - one-two times a day we got ORA-00060, and much more often - various blocking locks. The reason was that when a DML is performed on fact table of your bitmap join, the dimension table is getting locked in shared mode (LMODE=4). So during that DML you can't perform any updates on the dimension table - all transactions will wait for commit or rollback on the fact table.

Alexey Sergeyev

"Spendius" <spendius_at_muchomail.com> wrote in message news:aba30b75.0504140357.5d9dbbf8_at_posting.google.com...
> Hello,
> Are the risks of creating bitmap join indexes on several
> tables (2 or 3) that undergo many transactions the same as
> creating a classical bitmap index (? la 8i) ? Will we be
> likely to see many deadlocks arising in our alert.log and
> trc files ? I.e. is J. Lewis' warning (p. 97 of Practical
> Oracle8i) "bitmap indexes and OLTP systems do *not* mix"
> still valid ? Or maybe at Oracle they managed to deal with
> such indexes even in strongly DMLed tables in order to avoid
> ORA-00060 messages ? It would be nice actually.
>
> Thanks a lot.
> Sp
Received on Fri Apr 15 2005 - 05:08:03 CDT