ASA SQL User's Guide
Query Optimization and Execution
Semantic query transformations
Types of semantic transformations
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.
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 rewritten as X = 'abc'
X LIKE 'abc%'
is augmented with the predicates X < 'abcZ'
and X > = 'abc_'
where Z and _ represent the corresponding high values and low values for the collating sequence of this database. If the database is configured to store blank-padded strings, the second comparison operator is >, not >=, to ensure correct semantics.