Re: Why does Oracle sometimes favor an index based on column position in the index?
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx, Michael Moore <michaeljmoore@xxxxxxxxx>
- Date: Fri, 21 Jan 2011 05:44:50 -0800 (PST)
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
________________________________
Other related posts:
