which index

All posts relating to Oracle database administration.

Moderator: Tim...

Posts: 27
Joined: Sun Aug 05, 2012 11:45 am

which index

Postby vivek_k47 » Thu Feb 28, 2013 11:47 am

An index on a table T has to be created.It contains 10 million rows of data.The key columns have low cardinality.The queries generated against this table use a combination of multiple WHERE conditions involving the OR operator.Which index can be used for this criteria? Bitmap/unique/partitioned/reverse ?

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: which index

Postby Tim... » Thu Feb 28, 2013 7:56 pm


It depends...

Bitmap: Does the data change regularly? If so, the Bitmap is a bit no-no. You only want to use bitmap indexes for database that doesn't change too much or the overhead of the index maintenance gets massive.

unique: Is the data unique? Then use a unique constraint (and therefore unique index by default). You say low cardinatlity, which suggests there is lots of repeated values, so a unique index is not appropriate.

Reverse: Are you having problems with contention on the index, like hot blocks in the index, especially in RAC environments. Reverse key indexes may help here.

Partitioning: Is the table partitioned? If so, having local indexes can sometimes help from a maintenance perspective and may improve performance from a partition pruning perspective, but and index search on a 10 million row index is actually pretty quick. Depending on the height of the index, you may not see a performance degradation compared to an index on a few thousands rows. Partitioning has to be considered very carefully on a case-by-case basis.

B*Tree: The standard index for the OLTP environment. Of course, you have to question whether and index is relevant. If you queries only return a small subset of the rows in a table, an index is appropriate. If your queries return a large percentage of the data (like in DW databases), you probably want to avoid indexes in many cases.


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 7 guests