Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

MySQL cannot use a composite index in a lookup in which the 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?

share|improve this question
    
There is another answer in the linked question that states that PostgreSQL can use an index even if the leading columns are not used in the where clause: dba.stackexchange.com/questions/6115/… –  a_horse_with_no_name Oct 5 '11 at 18:10
    
@a_horse but only for filtering, not for reducing the number of index blocks that have to be read :) –  Jack Douglas Oct 5 '11 at 18:22
    
@a_horse mmm. I reworded slightly to remove confusion on PostgreSQL. Thanks! –  Derek Downey Oct 6 '11 at 14:44
add comment

2 Answers

up vote 5 down vote accepted

Loosely speaking, the CBO may choose to:

  1. build up a list of all possible values for the 'missing' leading columns (this can be done fairly efficiently from the index structure itself)
  2. iteratively perform range scans for each combination of missing columns and the column provided
  3. union the whole lot together in one result set

This is what is called a 'skip scan' in Oracle terminology. Skip scans work best when the number of possible values in step (1) is relatively small (that is small compared to the size of the index)

Under what circumstances can Oracle (at least in 11g) do a lookup without the left-most prefix columns existing in the query?

Oracle will use statistics to get an estimate of the cardinality of step (1) before weighing up if performing that many range scans will cost more than just scanning the whole index sequentially

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.