Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Index on low cardinality

Posted on 2013-12-10
7
Medium Priority
?
781 Views
Last Modified: 2014-01-21
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.
0
Comment
Question by:d27m11y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1332 total points
ID: 39709259
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
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 668 total points
ID: 39709445
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
 
LVL 32

Expert Comment

by:awking00
ID: 39709446
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 39780113
a penalty grade is not appropriate here
0
 

Author Comment

by:d27m11y
ID: 39793295
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1332 total points
ID: 39794141
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question