Solved

Index on low cardinality

Posted on 2013-12-10
7
730 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 333 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 167 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 333 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

688 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