Solved

Index on low cardinality

Posted on 2013-12-10
7
691 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
7 Comments
 
LVL 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

803 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