In my project most of the indexes having / includes low cardinality columns (like is_deleted_ind = 'Y' / 'N' ) and some of the indexes starting with this column, is it decreases the database performance.
Kindly help me on this.
Well it will depend upon the nature of the queries. But generally, it is better to have low cardinality columns as the last columns of the index and the highest cardinality column at the start of the index. This way the selectivity of the row becomes easier and hence better performance can be gained. So, I would say it is not the matter of performance decrement but it is more the case of there could be room for more performance gain.
Moreover, filtered indexes could be another suitable option depending upon your environment.
Here are two examples where the Low cardinality column could be bad as well as beneficial. You have to run the data population scripts in one batch and the examples in separate batches. You would be able to see the difference of cost in execution plans as well as through STATISTICS.
But as I said earlier, everything would depend upon your environment and you have to test whether it suits you or not.
Indexes with low cardinality may be ignored by the optimizer as they offer no assistance in generating a query plan for efficient execution of your query.
Where they can affect performance, is that every update/insert that affects the data in that index is extra overhead in the operation, for very little gain further down the line.
There is also the additional storage space needed.
All, or none, of these may be a concern in your environment - you need to test the different scenarios and determine your own conclusions.
answered Mar 15 '12 at 09:10 AM
Kev Riley ♦♦
Another thought to keep in mind is whether you are actually using the column as a predicate in your WHERE clause. No use in maintaining it unless it is a needed element.
answered Mar 15 '12 at 01:55 PM