Character String Beginning with Constant
A LIKE predicate in which a column is compared to a character string that begins with a character constant (not a wildcard) is transformed into three predicates: one predicate that uses the LIKE operator, one that uses the >= operator, and one that uses the < operator. For example:
country LIKE 'Ch%i%'
country LIKE 'Ch%i%' AND country >= 'Ch' AND country < 'Ci'
The first (LIKE) predicate is not optimizable, but the new predicates added by the transformation are.
When the character string begins with one more character constants and ends with a single "%", the first LIKE clause is eliminated. For example:
country LIKE 'Ch%'
country >= 'Ch' AND country < 'Ci'
Character String Without Wildcards
A LIKE predicate is transformed into a predicate that uses the = operator (and a NOT LIKE predicate is transformed into one that uses <>) when the character string does not contain any wildcards. For example:
country LIKE 'Chile'
country = 'Chile'
country NOT LIKE 'Chile'
country <> 'Chile'
'The situation is similar to those described above when a column is compared using the LIKE operator to a parameter whose value is unknown in advance (dynamic parameter, join column, etc.).
In this situation, the LIKE predicate is likewise transformed into three predicates: one LIKE predicate, one predicate using the >= operator, and one predicate using the < operator. For example:
country LIKE ?
is transformed into
country LIKE ? AND country >= InternallyGeneratedParameter AND country < InternallyGeneratedParameter
where the InternallyGeneratedParameters are calculated at the beginning of execution based on the value of the parameter.
- This transformation can lead to a bad plan if the user passes in a string that begins with a wildcard or a nonselective string as the parameter. Users can work around this possibility by writing the query like this (which is not optimizable):
(country || '') LIKE ?
Table of Contents