5.11.3 Equivalent exchange for OR conditions (converting to IN conditions)

If multiple = conditions are specified for the same column in an OR condition^#, the following equivalent exchange is performed:

The = conditions for the same column are converted to an IN condition.
The = conditions for the same column are converted to an IN condition, which is added outside of the OR condition.

If the = conditions are converted to an IN condition and then added outside of the OR condition, the resulting condition can be effective in narrowing down the search range; however, the workload for condition evaluation might increase when an IN condition is added.

#: If an array element reference with ANY specified (and identification number omitted) is specified in the search condition, equivalent exchange will not be performed even if the search conditions are the same. For specific examples, see (c) Example 3 in (2) Examples of search conditions on which equivalent exchange is not performed.

Important

OR conditions specified in the search conditions of WHERE clauses, search conditions of WHERE clauses in update SQL statements, ON search conditions of joined tables, and search conditions of HAVING clauses are subject to equivalent exchange.
When equivalent exchange is performed on search conditions, the indexes to be used during retrieval are determined based on the search conditions obtained after equivalent exchange has been applied.

The following shows examples of equivalent exchange. In the examples, C1, C2, and C3 are column names.

Organization of this subsection

(1) Examples of search conditions on which equivalent exchange is performed
(2) Examples of search conditions on which equivalent exchange is not performed
(3) Rules for equivalent exchange

(1) Examples of search conditions on which equivalent exchange is performed

(a) Example 1

[Figure]

Explanation:: The OR conditions are converted to an IN condition because the conditions specified in the OR conditions are all = conditions for column C1.

(b) Example 2

[Figure]

Explanation:: The = condition for column C1 specified in the OR condition is converted to an IN condition, which is then added outside of the OR condition.

(c) Example 3

[Figure]

Explanation:: Because the conditions specified in the OR conditions are = conditions for the same array element reference ("C1_ARRAY"[ANY(1)] = XXX), the OR conditions undergo equivalent exchange to become an IN condition.

To Page Top

(2) Examples of search conditions on which equivalent exchange is not performed

(a) Example 1

[Figure]

Explanation:: The conditions specified in the OR conditions are all = conditions for column C1, but equivalent exchange is not performed because they are specified inside a NOT condition.

(b) Example 2

[Figure]

Explanation:

OR conditions specified within AND conditions that are specified within OR conditions are not subject to equivalent exchange.

Although the OR condition indicated by 1. above meets the criteria for equivalent exchange to an IN condition, it does not undergo equivalent exchange because it is specified within an AND condition that is specified within the OR condition indicated by 2. above.

(c) Example 3

[Figure]

Explanation:: Since no identification numbers are specified for the array element references in the OR conditions, HADB assigns a different identification number to each array element reference. Therefore, the specified search conditions are determined not to be the same and equivalent exchange is not performed.

To Page Top

(3) Rules for equivalent exchange

If a comparison predicate is in any of the formats shown below, an IN condition is added outside the OR condition. Note that if a column specified in the column specification is an external reference column, equivalent exchange is not performed. For details about external reference columns, see Specification format and rules for subqueries in the manual HADB SQL Reference.

{column-specification|column-specification[ANY[(identification-number)]]}
    = {literal|datetime-information-acquisition-function|user-information-acquisition-function|dynamic-parameter}

{literal|datetime-information-acquisition-function|user-information-acquisition-function|dynamic-parameter}
    = {column-specification|column-specification[ANY[(identification-number)]]}

Equivalent exchange is not performed in the following cases:
- OR conditions specified in NOT conditions (see (a) Example 1 in (2) Examples of search conditions on which equivalent exchange is not performed)
- OR conditions specified in AND conditions (see (b) Example 2 in (2) Examples of search conditions on which equivalent exchange is not performed)

To Page Top