0 Replies Latest reply: Dec 21, 2013 8:32 AM by Kirupa2 RSS

    How optimizer choose between 2 indexes?

    Kirupa2

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