User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 25. Query Optimization       

Predicate analysis

A predicate is a conditional expression that, combined with the logical operators AND and OR, makes up the set of conditions in a WHERE or HAVING clause. In SQL, a predicate that evaluates to UNKNOWN is interpreted as FALSE.

A predicate that can exploit an index to retrieve rows from a table is called sargable. This name comes from the phrase search argument-able. Both predicates that involve comparisons with constants and those that compare columns from two or more different tables may be sargable.

The predicate in the following statement is sargable. Adaptive Server Anywhere can evaluate it efficiently using the primary index of the employee table.

SELECT *
FROM employee
WHERE employee.emp_id = 123

PLAN> employee (employee)

In contrast, the following predicate is not sargable. Although the emp_id column is indexed in the primary index, using this index does not expedite the computation because the result contains all, or all except one, row.

SELECT *
FROM employee
employee.emp_id <> 123

PLAN> employee (seq)

Similarly, no index can assist in a search for all employees whose first name ends in the letter "k". Again, the only means of computing this result is to examine each of the rows individually.

Examples 

In each of these examples, attributes x and y are each columns of a single table. Attribute z is contained in a separate table. Assume that an index exists for each of these attributes.

Sargable	Non-sargable
x = 10	x <> 10
x IS NULL	x IS NOT NULL
x > 25	x = 4 OR y = 5
x = z	x = y
x IN (4, 5, 6)	x NOT IN (4, 5, 6)
x LIKE 'pat%'	x LIKE '%tern'

Sometimes it may not be obvious whether a predicate is sargable. In these cases, you may be able to rewrite the predicate so that it is sargable. For each example, you could rewrite the predicate x LIKE 'pat%' using the fact that "u" is the next letter in the alphabet after "t": x >= 'pat' and x < 'pau'. In this form, an index on attribute x is helpful in locating values in the restricted range. Fortunately, Adaptive Server Anywhere makes this particular transformation for you automatically.

A sargable predicate that is used for indexed retrieval on a table is known as a matching predicate. A WHERE clause can have a number of matching predicates. Which is most suitable can depend on the join strategy. The optimizer re-evaluates its choice of matching predicates when considering alternate join strategies.

In other cases, a predicate may not be sargable simply because no suitable index exists. For example, consider the predicate X = Z. This predicate is sargable if these two attributes reside in different tables and at least one of them is the first attribute in an index. Should one of these conditions not be satisfied, the same predicate becomes non-sargable.