LIKE predicates involving patterns that are either literal constants or host variables are very common. Depending on the pattern, the optimizer may rewrite the LIKE predicate entirely, or augment it with additional conditions that could be exploited to perform indexed retrieval on the corresponding table. Additional conditions for LIKE predicates utilize the LIKE_PREFIX predicate, which cannot be specified directly in a query but appear in long and graphical plans when the query optimizer can apply the optimization.
In each of the following examples, assume that the pattern in the LIKE predicate is a literal constant or host variable, and X is a column in a base table:
X LIKE '%' is rewritten as X IS NOT NULL.
X LIKE 'abc%' is augmented with a LIKE_PREFIX predicate that is a sargable predicate (it can be used for index retrieval) and enforces
the condition that any value of X must begin with the characters abc. The LIKE_PREFIX predicate enforces the correct semantics
with multi-byte character sets and blank-padded databases.
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |