5.11.2 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.

The OR conditions specified in the search condition in the WHERE clause, the ON search condition for joined tables, and the search condition in the HAVING clause are subject to this equivalent exchange.

If equivalent exchange has been performed on search conditions, the indexes to be used during retrieval are determined based on the search conditions obtained after equivalent exchange.

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.

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:: "C1" = 100 and "C1" = 200 are specified on either side of the OR condition, but equivalent exchange is not performed on an OR condition that is specified inside an AND condition.

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 = literal
  
  Equivalent exchange is performed, even if the column specification and literal are specified in reverse order.
- column-specification = datetime-information-acquisition-function (or user-information-acquisition-function)
  
  Equivalent exchange is performed, even if the column specification and datetime information acquisition function (or user information acquisition function) are specified in reverse order.
- column-specification = dynamic-parameter
  
  Equivalent exchange is performed, even if the column specification and dynamic parameter are specified in reverse order.
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