low cardinality and highly concurrent db

[Vissu]

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

[Mark D Powell]

Re: low cardinality and highly concurrent db

vissuyk@yahoo.c om (Vissu) wrote in message news:<2bedd6a7. 0407292026.5297 89a1@posting.go ogle.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 --