5.11.2 Equivalent exchange for OR conditions (removing from the OR conditions)

If the same condition is specified in an OR condition^#, equivalent exchange is performed in such a manner that the same condition is removed from the OR condition. When the same condition is removed from the OR condition, the search condition sometimes becomes more effective in narrowing down the retrieval range. This can also reduce the workload for condition evaluation because the number of identical conditions in an OR condition is reduced to one.

#: 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 (d) Example 4 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:: Because condition "C1" = 100 is specified on both sides of the OR condition, "C1" = 100 is removed from the OR condition.

(b) Example 2

[Figure]

Explanation:: Because condition "C1" < CURRENT_DATE is specified on both sides of the OR condition, "C1" < CURRENT_DATE is removed from the OR condition.

(c) Example 3

[Figure]

Explanation:: Because condition "T1"."C1" = "T2"."C1" is specified on both sides of the OR condition, "T1"."C1" = "T2"."C1" is removed from the OR condition.

(d) Example 4

[Figure]

Explanation:: Because condition "C1" IS NULL is specified on both sides of the OR condition, "C1" IS NULL is removed from the OR condition.

(e) Example 5

[Figure]

Explanation:: Because condition "C1" IN(100,200,300) is specified on both sides of the OR condition, "C1" IN (100,200,300) is removed from the OR condition.

(f) Example 6

[Figure]

Explanation:: Because condition "C1" BETWEEN 100 AND 300 is specified on both sides of the OR condition, "C1" BETWEEN 100 AND 300 is removed from the OR condition.

(g) Example 7

[Figure]

Explanation:: Because the conditions specified in the OR conditions are all "C1" = 100, they are converted to a single = condition.

(h) Example 8

[Figure]

Explanation:: Because the conditions specified in the OR conditions are all "C1" <> 100, they are converted to a single <> condition.

(i) Example 9

[Figure]

Explanation:: Because condition "C1_ARRAY"[ANY(1)] = 100 is specified on both sides of the OR condition, "C1_ARRAY"[ANY(1)] = 100 is removed from the OR condition.

(j) Example 10

[Figure]

Explanation:: Because condition ("C1","C2") IN ((1,2),(3,4)) is specified on both sides of the OR condition, ("C1","C2") IN ((1,2),(3,4)) is removed from the OR condition.

To Page Top

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

(a) Example 1

[Figure]

Explanation:: Equivalent exchange is not performed because "T1"."C1" = "T2"."C1" and "T2"."C1" = "T1"."C1" are regarded as different conditions.

(b) Example 2

[Figure]

Explanation:: "C1" = 100 is specified on both sides of the OR condition, but no equivalent exchange is performed because this OR condition is specified inside a NOT condition.

(c) Example 3

[Figure]

Explanation:: "C1" = 100 OR "C1" > 200 is specified on both sides of the OR condition, but equivalent exchange is not performed on an OR condition specified inside an AND condition.

(d) Example 4

[Figure]

Explanation:: The same condition "C1_ARRAY"[ANY] = 100 appears on both sides of the OR condition, but since no identification number is specified for the array element reference, HADB assigns different identification numbers 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 following formats, a condition inside the OR condition is removed from the OR condition:
```
{column-specification|column-specification[ANY[(identification-number)]]}
    comparison-operator {literal|datetime-information-acquisition-function|user-information-acquisition-function}
```
```
{literal|datetime-information-acquisition-function|user-information-acquisition-function}
    comparison-operator {column-specification|column-specification[ANY[(identification-number)]]}
```
```
{column-specification|column-specification[ANY[(identification-number)]]}*
    comparison-operator {column-specification|column-specification[ANY[(identification-number)]]}*
```
#

If a column specification or an array element reference specifying ANY of a column specification in an array value expression is specified in reverse order, it is considered a different condition and equivalent exchange is not performed (refer to (a) Example 1 in (2) Examples of search conditions on which equivalent exchange is not performed).
If the NULL predicates are specified in either of the following formats, the condition in the OR condition is removed from the OR condition:
```
column-specification^# IS [NOT] NULL
```
#

When an array-type column is specified in the column specification, the extraction outside of the OR condition is not performed.

In the case of the following format with an IN predicate for which a row value constructor is not specified, the conditions within the OR condition are extracted outside the OR condition.

{column-specification|column-specification[ANY[(identification-number)]]}
    [NOT] IN ({literal|datetime-information-acquisition-function|user-information-acquisition-function}
             [,{literal|datetime-information-acquisition-function|user-information-acquisition-function}]...)

In the case of the following format with an IN predicate for which a row value constructor is specified, the conditions within the OR condition are extracted outside the OR condition.

row-value-constructor-1 [NOT] IN (row-value-constructor-2 [,row-value-constructor-2]...)
  row-value-constructor-1::=(column-specification [,column-specification]...)
  row-value-constructor-2::=({literal|datetime-information-acquisition-function|user-information-acquisition-function}
               [,{literal|datetime-information-acquisition-function|user-information-acquisition-function}]...)

If the BETWEEN predicates are specified in either of the following formats, the condition in the OR condition is removed from the OR condition:

{column-specification|column-specification[ANY[(identification-number)]]}
    [NOT] BETWEEN {literal|datetime-information-acquisition-function|user-information-acquisition-function}
                AND {literal|datetime-information-acquisition-function|user-information-acquisition-function}

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

To Page Top