Solved

Index on low cardinality

Posted on 2013-12-10
7
668 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 31

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now