How does Oracle handle composite index lookups?
In Oracle, we have a feature called Index skip-scan that makes it possible to benefit from composite indexes even if the leading (left most) part of the index is not specified in the WHERE condition. This is a 9i New Feature, documented here:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm