5.11.3 Equivalent exchange for OR conditions (equivalent exchange to derived tables for which the UNION ALL set operation is specified)

Explanation:

• In the SQL statement before equivalent exchange, a joined table is specified in the FROM clause and OR conditions are specified in the WHERE clause (see the underscored portion in the preceding example). Therefore, the SQL statement is subject to equivalent exchange.

• The search conditions that were specified with OR conditions in the SQL statement before equivalent exchange are, after equivalent exchange, specified as search conditions of query specifications in separate set operation operands. If two OR conditions are specified as shown in the SQL statement before equivalent exchange, three set operation operands are generated after equivalent exchange.

Explanation:

• In the SQL statement before equivalent exchange, a comma join is specified in the FROM clause and an OR condition is specified in the WHERE clause (see the underscored portions). Therefore, the SQL statement is subject to equivalent exchange.

• The search conditions that were specified with OR conditions in the SQL statement before equivalent exchange are, after equivalent exchange, specified as search conditions of query specifications in separate set operation operands. The search condition that was specified on the left side of the AND condition in the SQL statement before equivalent exchange are, after equivalent exchange, specified as search conditions of query specifications in all set operation operands.

All of the following conditions must be met:

• 0 is not specified for the adb_sql_exe_hashtbl_area_size operand in the server definition or client definition.

• 0 is not specified for the adb_sys_uthd_num operand in the server definition.

• 0 is not specified for the adb_sql_exe_max_rthd_num operand in the server definition or client definition.

All of the following conditions must be met:

• The SQL statement is a retrieval SQL statement.

• An HADB server whose version is 04-03 or later has collected cost information from any of tables specified in the SQL statement.

• The SQL statement does not have a query specification that includes an external reference column.

All of the following conditions must be met:

1. Conditions for selection expressions

   • ROW is not specified in the selection expression.

2. Conditions for a table reference specified in the FROM clause

   • The number of tables specified in the selection expression does not exceed 16.

   • Comma joins are specified in the FROM clause.

     Note that this condition is met if only comma joins are specified or if both comma joins and joined tables are specified.

     The following shows the types of tables that can be specified. This condition is met even if the specifications of the following types of tables co-exist:

     • Base table (Note that equivalent exchange does not take place if an archivable multi-chunk table is specified.)

     • Joined table (Note that equivalent exchange does not take place if FULL OUTER JOIN is specified.)

     • Derived table (A table subquery, query name, or viewed table applies.)

All of the following conditions must be met:

• No OR condition is specified in the join specifications of joined tables.

• The join conditions of tables to which the columns specified in OR conditions belong satisfy the following condition:

  • At least one join condition in the column-specification=column-specification format exists between the tables to which the columns specified in OR conditions.

• When HADB performs equivalent exchange to derived tables for which the UNION ALL set operation is specified, a join condition for all join-target tables must exist in the search condition of the query specification in each set operation operand.

All of the following conditions must be met:

1. Two or more conditions are not specified in OR conditions in the conditions specified by using the AND logical operator.

   Examples:

   • Example of a search condition that satisfies the conditions for equivalent exchange to take place

     condition-1 AND condition-2 AND (condition-3 OR condition-4 OR condition-5)

     The conditions for equivalent exchange to take place are satisfied because there is only one condition that includes OR conditions.

   • Example of a search condition that does not satisfy the conditions for equivalent exchange to take place

     (condition-1 OR condition-2) AND condition-3 AND (condition-4 OR condition-5)

     The conditions for equivalent exchange to take place are not satisfied because there are two conditions that include OR conditions.

   • If multiple OR conditions are nested, the conditions for equivalent exchange to take place are satisfied.

   • The number of tables specified in successive search conditions other than OR conditions is not taken into account (the specifications of these tables do not need to be join specifications).

   • An OR condition with NOT specified does not satisfy the conditions for equivalent exchange to take place.

2. Columns of joined tables are not specified in OR conditions.

   However, the conditions for equivalent exchange to take place are satisfied if a joined table is converted to a comma join.

3. No subquery is specified with an OR condition.

4. Scalar function RANDOM or RANDOM_NORMAL is not specified with an OR condition.

5. The number of OR logical operators does not exceed 15.

6. An OR condition includes a search condition for two or more tables.

7. The search condition in each set operation operand after equivalent exchange includes one or more search conditions in any of the following formats. In addition, logical operator OR or NOT is not specified for those conditions.

   • Comparison predicate

     • column-specification comparison-operator {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}
     • {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function} comparison-operator column-specification

   • BETWEEN predicate

     column-specification BETWEEN {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}
                AND {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}

   • IN predicate

     column-specification IN ({literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}
             [,{literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}]...)

   • LIKE predicate

     column-specification LIKE pattern-character-string [ESCAPE escape-character]
       pattern-character-string ::= {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}
       escape-character ::= {literal|dynamic-parameter}

   • LIKE_REGEX predicate

     column-specification LIKE_REGEX regular-expression-character-string [FLAG {I|IGNORECASE}]
       regular-expression-character-string ::= literal

   • NULL predicate

     column-specification IS NULL

   Note

   If only literals are specified in a scalar operation, that scalar operation might be treated as a literal. For details about scalar operations equivalent to literals, see the table Conditions under which value expressions are equivalent to literals under Rules in Specification format and rules for value expressions in the manual HADB SQL Reference.

If a query specification is included in the following locations, the HADB server checks whether to perform equivalent exchange:

• The query expression body in a query expression

• Scalar subquery

• Table subquery

• Set operation operands of a set operation

  If derived tables with UNION ALL specified are created when equivalent exchange is performed for an SQL statement, the maximum number of set operations might be exceeded. In such a case, however, equivalent exchange of the SQL statement continues.

• WITH list element

  Equivalent exchange is not performed for query specifications of recursive members.

• Query expression in the CREATE VIEW statement