Run-time side effect of dropping a composite unused index

Unused Index although they seem not needed , dropping them must be carefully tested  as it can have some side effect. As explained by Jonathan Lewis, due to index sanity check cardinality miss estimate can happen after dropping the unused index.

“The reason for this is the “index sanity check”. When the optimizer is doing its cardinality estimates, if it see equality conditions on the set of columns that make up an index it can use the distinct_keys statistic from the index in the calculation rather than using the standard calculation of multiplying together the num_distinct of the separate columns. ”

There is also a good example of the impact this can have on the query execution plan(switching join method from hash join to nested loop due to the drop of the estimated number of rows returned).Link

In this post i will show another interesting case when this can have an impact on query performance.As Franck Pachot Said :

Continue reading