The optimizer supports a special optimization for exploiting IN predicates on indexed columns. This optimization also applies equally to multiple predicates on the same indexed column that are OR'ed together, since the two are semantically equivalent. To enable the optimization, the IN-list must contain only constants, or values that are constant during one execution of the query block, such as outer references.

When the optimizer encounters a qualifying IN-list predicate, and the IN-list predicate is sufficiently selective to consider indexed retrieval, the optimizer converts the IN-list predicate into a nested loops join. The following example illustrates how the optimization works.

Suppose you have the following query, which lists all the orders for two sales reps:

SELECT *
FROM SalesOrders
WHERE SalesRepresentative = 902 OR SalesRepresentative = 195;

This query is semantically equivalent to:

SELECT *
FROM SalesOrders
WHERE SalesRepresentative IN (195, 902);

The optimizer estimates the combined selectivity of the IN-list predicate to be low enough to warrant indexed retrieval. So, the optimizer treats the IN-list as a virtual table, and joins this virtual table to the SalesOrders table on the SalesRepresentative attribute. While the net effect of the optimization is to include an additional join in the access plan, the join degree of the query is not increased, so optimization time should not be affected.

There are two main advantages of this optimization. First, the IN-list predicate can be treated as a sargable predicate and exploited for indexed retrieval. Second, the optimizer can sort the IN-list to match the sort sequence of the index, leading to more efficient retrieval.

The short form of the access plan for the above query is:

SalesOrders<FK_SalesRepresentative_EmployeeID>

 See also