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
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog
Who is online
Users browsing this forum: No registered users and 1 guest