Re: Why does Oracle sometimes favor an index based on column position in the index?

COLUMNA is your primary key, making it unique in the data.  Yet you are not 
looking for unique values in COLUMNB:

AND TABLEA.COLUMNB IN (‘X’,’Y’)

COLUMNA  and COLUMNB pairs in TABLEA are unique so Oracle chose the non-unique  
column index (IDX01) to 'pare down' the data; unfortunately it now has  to walk 
through the unique values of COLUMNA to find a  match with  records in TABLEB.  
Since COLUMNA is quite selective Oracle won't  perform an INDEX SKIP SCAN on 
IDX02 thus it must use (with your original  configuration) IDX01.  Adding IDX03 
to the mix allowed Oracle to scan  that  index for the non-unique COLUMNB values 
then match up the COLUMNA  values from that same index to  return the unique 
ROWIDs of your result set.


 
David Fitzjarrell
 




________________________________


      

--
http://www.freelists.org/webpage/oracle-l