Index on low cardinality

Table has millions rows and 3-4 values for Status column. Which index is better on Status column and why ?

Is it bitmap index, I read that bitmap index is used for low cardinality but am not sure why it is used for low cardinality.
d27m11yAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
not enough information to determine.

if your table and that status column in particular are highly volatile then updates, inserts, deletes will cause a lot of changes to the bitmap index which is bad for performance.

if your table, and that column in particular is relatively static then a bitmap index maybe a reasonable choice for read performance.

also note, depending on skew a normal index may be fine.
in the case of extreme skew, a set of function-based indexes may be ideal.

best solution will depend on full use case analysis.

 Low cardinality is not really a significant criteria on its own.
0
 
Franck PachotConnect With a Mentor Commented:
Hi,

>> I read that bitmap index is used for low cardinality
That's wrong. Whether it is bitmap or not, and index access is efficient only when you retreive a small mount of rows. On static tables, Bitmap indexes can be created on columns that have low cardinality but will be efficient only when they are combined so that the it is finally a low cardinality access to the table.


For your status column, partitioning is probably the best you can do.

Regards,
Franck.
0
 
awking00Commented:
This link provides a nice in-depth analysis of precisely what sdstuber stated -
http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html

No points, please.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
a penalty grade is not appropriate here
0
 
d27m11yAuthor Commented:
This was an interview question and someone has asked me to suggest a solution. I was not sure about the solution. I do not have any enough info if it has more inserts/updates or if it is just read only..
0
 
sdstuberConnect With a Mentor Commented:
Hopefully you gave the same answer I did in the interview.  Without more information, simply using "low cardinality" is not a reason to suggest bitmap indexes.

The reason you might want to use them with relatively static data is because a single entry in a bitmap index points to a range of rowids.  As opposed to a normal index where entries are one-to-one.  So, with a bitmap index you have a smaller index to scan through to try to find relevant entries.

The reason you want static data with bitmap indexes is, if the data changes you can't simply change the corresponding row in the index, but must rebuild the ranges and associated bitmaps which is a more expensive operation.

I do recommend reading the link awking00 posted above too.  It contains examples to illustrate what has been described above.
0
All Courses

From novice to tech pro — start learning today.