ASA SQL User's Guide
  Query Optimization and Execution
    Semantic query transformations
      Types of semantic transformations

IN-list optimization

The Adaptive Server Anywhere 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 ORed together, since the two are semantically equivalent. To enable the optimization, the IN-list must contain only constants.

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-loop join. The following example illustrates how the optimization works.

Suppose we have the query

SELECT *
FROM sales_order
WHERE sales_rep = 142 or sales_rep = 1596

that lists all of the orders for these two sales reps. This query is semantically equivalent to

SELECT *
FROM sales_order
WHERE sales_rep IN (142, 1596)

The optimizer estimates the combined selectivity of the IN-list predicate to be high enough to warrant indexed retrieval. Consequently the optimizer treats the IN-list as a virtual table, and joins this virtual table to the sales_order table on the sales_rep 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

IN JNL sales_order<ky_so_employee_id>