View transformations

This section describes using LIKE transformations as a comparison operator. -

LIKE transformations and optimizations are disabled when you use locale-based collation. See "Character-based collation in " in the Modified: db/derby/docs/trunk/src/tuning/rtuntransform345.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/rtuntransform345.dita?rev=1609827&r1=1609826&r2=1609827&view=diff ============================================================================== --- db/derby/docs/trunk/src/tuning/rtuntransform345.dita (original) +++ db/derby/docs/trunk/src/tuning/rtuntransform345.dita Fri Jul 11 20:43:23 2014 @@ -18,25 +18,27 @@ limitations under the License. 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. -

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%'

becomes +

For example,

+country LIKE 'Ch%i%' +

becomes

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%'

becomes +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%' +

becomes

country >= 'Ch' -AND country < 'Ci'

+AND country < 'Ci'

Modified: db/derby/docs/trunk/src/tuning/rtuntransform443.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/rtuntransform443.dita?rev=1609827&r1=1609826&r2=1609827&view=diff ============================================================================== --- db/derby/docs/trunk/src/tuning/rtuntransform443.dita (original) +++ db/derby/docs/trunk/src/tuning/rtuntransform443.dita Fri Jul 11 20:43:23 2014 @@ -18,18 +18,22 @@ limitations under the License. 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. -

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'

becomes -country = 'Chile'

and -country NOT LIKE 'Chile'

becomes -country <> 'Chile'

Predicates that use the = operator are optimizable. Predicates that use the <> operator are sargable.

For example,

+country LIKE 'Chile' +

becomes

+country = 'Chile' +

and

+country NOT LIKE 'Chile' +

becomes

+country <> 'Chile' +

Predicates that use the = operator are +optimizable. Predicates that use the <> operator are sargable.

Modified: db/derby/docs/trunk/src/tuning/rtuntransform472.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/rtuntransform472.dita?rev=1609827&r1=1609826&r2=1609827&view=diff ============================================================================== --- db/derby/docs/trunk/src/tuning/rtuntransform472.dita (original) +++ db/derby/docs/trunk/src/tuning/rtuntransform472.dita Fri Jul 11 20:43:23 2014 @@ -18,25 +18,26 @@ limitations under the License. Unknown parameter +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, and the like). -

'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 +

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. +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): +is not optimizable): (country || '') LIKE ?