patternMinor
How does Oracle handle composite index lookups?
Viewed 0 times
howhandlelookupscompositedoesindexoracle
Problem
MySQL cannot use a composite index in a lookup in which the
MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index.
A quote from this answer on PostgreSQL caught my attention:
This is somewhat different in Oracle 11 which can sometimes also use columns that are not at the beginning of the index definition.
Under what circumstances can Oracle (at least in 11g) do a lookup without the left-most prefix columns existing in the query?
WHERE condition doesn't include the columns forming a left-most prefix:MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index.
A quote from this answer on PostgreSQL caught my attention:
This is somewhat different in Oracle 11 which can sometimes also use columns that are not at the beginning of the index definition.
Under what circumstances can Oracle (at least in 11g) do a lookup without the left-most prefix columns existing in the query?
Solution
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
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm
Context
StackExchange Database Administrators Q#6539, answer score: 6
Revisions (0)
No revisions yet.