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

From: David Fitzjarrell <>
Date: Fri, 21 Jan 2011 05:44:50 -0800 (PST)
Message-ID: <>

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


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


Received on Fri Jan 21 2011 - 07:44:50 CST

Original text of this message