0 Replies Latest reply on Dec 21, 2013 2:32 PM by Kirupa2

    How optimizer choose between 2 indexes?


      Hi Team,


      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..