Hi,
We are using application related to Human Task Workflow. In one of the HWF tables, we have a low cardinality column (4 or 5 unique values for over a million rows in the table).
There was a index on this low cardinality column. We dropped this column and the performance of the select query involving this column improved by 100%. We achieved the same result by adding a bitmap index on this column instead of the default B-tree index which gets created.
Of course, since it's an OLTP table, we dropped the bitmap index later on this column.
My reasoning for implementing this change (of dropping B-tree index column & resulting performance improvement) is that this low cardinality column shouldn't be having a B-tree index on this. However, I am looking for help in trying to find more data points to justify this change.
I look forward for views / suggestions / more datapoints from experience DBAs on this.
Thanks in advance,
Prash