Re: low cardinality and highly concurrent db

vissuyk_at_yahoo.com (Vissu) wrote in message news:<2bedd6a7.0407292026.529789a1_at_posting.google.com>...
> Hi All,
>
> We have one column with low cardinality, 4 or 5 unique values across
> 50 mil rows.
>
> Our query has this colunmn as a predicate. Binary index is not
> helping. I am tempted to create bitmap index but the general myth is
> there could be lot of contentions. We have a highly active OLTP system
> with concurrent DMLs.
>
> When people say that contentions due to bitmap indexes are massive,
> what excatly does it mean? I am not convinced that it locks the entire
> table.
>
> Can anyone share their experiences with bitmap indexes in OLTP systems
> ?. I want to know % degradation during DML due to bitmap index if
> possible.
>
> Thanks a lot for any advice
>
> Vissu

Whan a newgroup has subgroups you should generally post in the subgroups and not in the newsgroup.

A bitmap index entry can cover thousands of rows. I forget the exact number but it can be more than 20,000 rows. When you change a table row you also get a lock on the associated index rows for that table row. Now you have a lock on an index row that covers thousands of table rows so in effect you now have a row locks on thousands of rows.  This is why bitmap indexes are not usable in an OLTP situation.

If the queries in question that reference the low cardinality column have or could reference another column also then you might be able to rebuild you single column index as a multi-column index and beat the problem that way.

HTH -- Mark D Powell -- Received on Fri Jul 30 2004 - 16:08:51 CEST