5.11.4 Equivalent exchange for scalar operations

When one of the terms of a search condition specifies a scalar operation that contains a column specification, the scalar operation is transposed. That is, the search condition undergoes equivalent exchange so as to leave only a column specification. A scalar operation is transposed when all of the following conditions are met:

The operand satisfies either of the following conditions:
- The operator is an arithmetic operator (addition or subtraction) between a column specification and a literal.
- The operator is a datetime operator between a column specification and a labeled duration.
The data type of the column specification is SMALLINT, INTEGER, TIME, DATE, or TIMESTAMP.
The scalar operation is not nested.

When a search condition is subjected to equivalent exchange, the indexes to be used during retrieval are determined based on the search conditions after equivalent exchange.

The following shows examples of equivalent exchange. In the examples, C1 is a column name.

Organization of this subsection

(1) Examples where equivalent exchange is performed
(2) Examples where equivalent exchange is not performed
(3) Rules for equivalent exchange

(1) Examples where equivalent exchange is performed

(a) Example 1

[Figure]

Explanation:: A term that includes a column specification has the scalar operation +10. By equivalent exchange, this scalar operation is moved to the right-hand side, leaving the condition with just a column specification. Because the format after equivalent exchange is column-specification comparison-operator literal, an index will be used during retrieval.

(b) Example 2

[Figure]

Explanation:: A term that includes a column specification has the scalar operation +1 DAY. By equivalent exchange, this scalar operation is moved to the right-hand side, leaving just a column specification in the condition. Because the format after equivalent exchange is column-specification comparison-operator literal, an index will be used during retrieval.

(c) Example 3

[Figure]

Explanation:: A term that includes a column specification has the scalar operation +10. By equivalent exchange, this scalar operation is moved to the right-hand side, leaving just a column specification in the condition. Because the format after equivalent exchange is column-specification BETWEEN literal, an index will be used during retrieval.

(d) Example 4

[Figure]

Explanation:: A term that includes a column specification has the scalar operation +10. By equivalent exchange, this scalar operation is moved to the right-hand side, leaving just a column specification in the condition. Because the format after equivalent exchange is column-specification IN literal, an index will be used during retrieval.

To Page Top

(2) Examples where equivalent exchange is not performed

(a) Example 1

[Figure]

Explanation:: Equivalent exchange does not take place because the scalar operation that includes the column specification is nested.

To Page Top

(3) Rules for equivalent exchange

When a comparison predicate is in any of the following formats, equivalent exchange is performed by transposing the scalar operation to leave only the column specification.
- scalar-operation-containing-column-specification comparison-operator literal
- literal comparison-operator scalar-operation-containing-column-specification
Equivalent exchange does not occur if all of the following conditions are met:
- The scalar operation that includes the column specification specifies YEAR or MONTH as a labeled duration.
- The comparison operator of the comparison predicate is <>, !=, or ^=.
When a BETWEEN predicate is used in any of the following formats, equivalent exchange is performed by transposing the scalar operation to leave only the column specification in the condition.
- scalar-operation-including-column-specification BETWEEN literal AND literal
- scalar-operation-including-column-specification NOT BETWEEN literal AND literal
Equivalent exchange does not occur if all of the following conditions are met:
- The scalar operation that includes the column specification specifies YEAR or MONTH as a labeled duration.
- NOT BETWEEN is specified in the BETWEEN predicate.
When an IN predicate is used in any of the following formats, equivalent exchange is performed by transposing the scalar operation to leave only the column specification in the condition.
- scalar-operation-including-column-specification IN (literal,...)
- scalar-operation-including-column-specification NOT IN (literal,...)
Equivalent exchange will not take place if the scalar operation that includes the column specification specifies YEAR or MONTH as a labeled duration.

To Page Top