5.11.1 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.
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.
When 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
(a) Example 1
![[Figure]](GRAPHICS/F0000050.GIF)
- 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]](GRAPHICS/F0000051.GIF)
- 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]](GRAPHICS/F0000052.GIF)
- 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]](GRAPHICS/F0000053.GIF)
- 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]](GRAPHICS/F0000054.GIF)
- 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]](GRAPHICS/F0000055.GIF)
- 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]](GRAPHICS/F0000056.GIF)
- Explanation:
-
Because the conditions specified in the OR conditions are all "C1" = 100, they are converted to a single = condition.
(h) Example 8
![[Figure]](GRAPHICS/F0000057.GIF)
- Explanation:
-
Because the conditions specified in the OR conditions are all "C1" <> 100, they are converted to a single <> condition.
(2) Examples of search conditions on which equivalent exchange is not performed
(a) Example 1
![[Figure]](GRAPHICS/F0000058.GIF)
- 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]](GRAPHICS/F0000059.GIF)
- 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]](GRAPHICS/F0000060.GIF)
- 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.
(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 comparison-operator literal
Equivalent exchange is performed, even if the column specification and literal are specified in reverse order.
-
column-specification comparison-operator 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 comparison-operator column-specification
If the columns specified as the column specification are specified in reverse order, equivalent exchange is not performed because they are regarded as different conditions (see (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 NULL
-
column-specification IS NOT NULL
-
-
If the IN predicates are specified in either of the following formats, the condition in the OR condition is removed from the OR condition:
-
column-specification IN (value-expression,...)
Equivalent exchange is performed if only literals, datetime information acquisition functions, or user information acquisition functions are specified in the value expressions.
-
column-specification NOT IN (value-expression,...)
Equivalent exchange is performed if only literals, datetime information acquisition functions, or user information acquisition functions are specified in the value expressions.
-
-
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 BETWEEN value-expression-1 AND value-expression-2
Equivalent exchange is performed if only literals, datetime information acquisition functions, or user information acquisition functions are specified in value expressions 1 and 2.
-
column-specification NOT BETWEEN value-expression-1 AND value-expression-2
Equivalent exchange is performed if only literals, datetime information acquisition functions, or user information acquisition functions are specified in value expressions 1 and 2.
-
-
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)
-