which index

[vivek_k47]

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 ?

[Tim...]

Hi.

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.

Cheers

Tim...