Using predicates in queries

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. SQL Anywhere can evaluate it efficiently using the primary index of the Employees table.

SELECT *
FROM Employees
WHERE Employees.EmployeeID = 102;

In the plan, this appears as: Employees<Employees>.

In contrast, the following predicate is not sargable. Although the EmployeeID 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 Employees
where Employees.EmployeeID <> 102;

In the plan, this appears as: Employees<seq>.

Similarly, no index can assist in a search for all employees whose given 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 SalesOrders
WHERE YEAR ( OrderDate ) ='2000';

To avoid using a function, you can rewrite a query to make it sargable. For example, you can rephrase the above query:

SELECT * 
FROM SalesOrders
WHERE OrderDate > '1999-12-31'
AND OrderDate < '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 OrderDate that holds the date of an order, you can create a computed column called OrderYear that holds the values for the year extracted from the OrderDate column.

ALTER TABLE SalesOrders
ADD OrderYear INTEGER
COMPUTE ( YEAR( OrderDate ) );

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

CREATE INDEX IDX_year
ON SalesOrders ( OrderYear );

If you then execute the following statement, the database server recognizes that there is an indexed column that holds that information and uses that index to answer the query.

SELECT * FROM SalesOrders
WHERE YEAR( OrderDate ) = '2000';

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( OrderDate ) had returned a string instead of an integer, the optimizer would not have substituted the computed column for the expression, and 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.

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'
x = 20 - 2	x + 2 = 20
X IS NOT DISTINCT FROM Y+1
X IS DISTINCT FROM Y+1

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, SQL 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 many matching predicates. The most suitable predicate can depend on the join strategy. The optimizer re-evaluates its choice of matching predicates when considering alternate join strategies. See Discovery of exploitable conditions through predicate inference.

Discuss this page in DocCommentXchange.