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

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, HAVING, or ON 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 . Predicates that involve comparisons of a column with constants, other columns, or expressions 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

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
where employee.emp_id <> 123

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.

Functions 

In general, a predicate that has a function on the column name is not sargable. For example, an index would not be used on the following query:

SELECT * from sales_order
WHERE year(order_date)='2000'

You can sometimes rewrite a query to avoid using a function, thus making it sargable. For example, you can rephrase the above query:

SELECT * from sales_order
WHERE order_date > '1999-12-31'
AND order_date < '2001-01-01'

A query that uses a function becomes sargable if you store the function values in a computed column and build an index on this column. A computed column is a column whose values are obtained from other columns in the table. For example, if you have a column called order_date that holds the date of an order, you can create a computed column called order_year that holds the values for the year extracted from the order_date column.

ALTER TABLE sales_order
ADD order_year INTEGER
COMPUTE year(order_date)

You can then add an index on the column order_year in the ordinary way:

CREATE INDEX idx_year
ON sales_order (order_year)

If you then execute the following statement

SELECT * from sales_order
WHERE year(order_date) = '2000'

the server recognizes that there is an indexed column that holds that information and uses that index to answer the query.

The domain of the computed column must be equivalent to the domain of the COMPUTE expression in order for the column substitution to be made. In the above example, if year(order_date) had returned a string instead of an integer, the optimizer would not have substituted the computed column for the expression, and consequently the index idx_year could not have been used to retrieve the required rows.

For more information about computed columns, see Working with computed columns.

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.

Sometimes it may not be obvious whether a predicate is sargable. In these cases, you may be able to rewrite the predicate so 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 used for indexed retrieval on a table is 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.