i have a situation where one query is choosing wrong index and performing bad.
i will explain with simple example.
select a,r,t,u from table where a=? and b=? and c = ?
we have composite primary key index ind1 (a+b+c)...
The query is using the above index and runing fast..
lately we created another composite index for different purpose on the same table ind2 (b+d+e+f).
After creating the index 2. the queries what use to choose ind1 and ran fast started using ind2 and runing slow....
I want to understand why optimizer choosing the ind2 eventhough ind1 is better index here??
both indexes is having stats..