How does Oracle handle composite index lookups

indexoracle

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?

Best Answer

Loosely speaking, the CBO may choose to:

build up a list of all possible values for the 'missing' leading columns (this can be done fairly efficiently from the index structure itself)
iteratively perform range scans for each combination of missing columns and the column provided
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

Related Solutions

Sql-server – Filtered indices: Why include the filtered-on field

This has been previously raised on Connect and Closed as "Won't Fix".

We'll close this because it seems a narrow scenario and we don't see doing anything about it in the forseeable future.

That's quite disappointing IMO. Perhaps upvote and comment that item.

Edit: BOL has an example of using WHERE EndDate IS NOT NULL in a filtered index and that does not do a key lookup but replacing IS NOT NULL with IS NULL does lead to a lookup so it appears as though sometimes it is avoided...

Mysql – How are multiple indexes used in a query by MySQL

MySQL is capable of doing an INDEX MERGE which allows MySQL to search on both indexes and merge the results. Of course, the MySQL Query Optimizer is the arbitrator of this process.

From my perspective, just looking at your query and its WHERE clause, I would create a compound index on your table and an additional index. Which compound index, (a,b) or (b,a) ?

I would next check the cardinality of each column as follows:

SELECT COUNT(DISTINCT a) a_count FROM `table`;
SELECT COUNT(DISTINCT b) b_count FROM `table`;

Based on these counts

If a_count < b_count, then I would create compound index (a,b) and an index on b
If a_count > b_count, then I would create compound index (b,a) and an index on a
If a_count = b_count, then I would create one pair or other

For more information, please read the following links:

Best Answer

Related Solutions

Sql-server – Filtered indices: Why include the filtered-on field

Mysql – How are multiple indexes used in a query by MySQL

Related Question