469,936 Members | 2,413 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,936 developers. It's quick & easy.

low cardinality and highly concurrent db

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
Jul 19 '05 #1
1 6462
vi*****@yahoo.com (Vissu) wrote in message news:<2b**************************@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 --
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Rizyak | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.