5.16 Conditions for applying the SQL parallel execution function

When all of the following conditions are met, the SQL parallel execution function is applied to the retrieval SQL statement (SELECT statement).

For details about the SQL parallel execution function, see SQL parallel execution function in the HADB Setup and Operation Guide.

■ Conditions for using the SQL parallel execution function

One of the following conditions must be met:

• Y is specified for the adb_clt_sql_parallel_exec operand in the client definition.

• The SQL parallel execution specification is specified in the SQL statement.

■ Conditions related to execution environments

The following two sets of conditions must be met:

1. Conditions related to server definitions

   All of the following conditions must be met:

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

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

2. Conditions related to transactions that execute retrieval SQL statements

   All of the following conditions must be met:

   • The transaction access mode is read-only mode.

   • The transaction isolation level is READ COMMITTED.

■ Conditions related to retrieval target tables

The retrieval target table of the retrieval SQL statement (the base table specified in the FROM clause of the retrieval SQL statement) must meet all of the following conditions:

• The retrieval target table is a column store table.

• The retrieval target table is a multi-chunk table.

• Cost information for the retrieval target table has been collected by an HADB server of version 04-03 or later.

• The data DB area that stores the retrieval target table consists of two or more DB area files.^#

#

This condition does not apply if you are using a cloud storage facility.

■ Conditions related to SQL statements

All of the following conditions must be met:

• The SQL statement is a SELECT statement.

• No set operation is specified in the SQL statement.

• No subquery is specified in the SQL statement.

Note

The SQL parallel execution function is not applied to COPY statements of retrieval SQL statements.

■ Conditions related to query specifications in SQL statements

The following six conditions must be met:

1. SELECT DISTINCT is not specified.

   However, even if SELECT DISTINCT is specified, if the HADB server determines that SELECT DISTINCT does not need to be executed, the SQL parallel execution function will be applied.

2. Only one base table is specified in the FROM clause. Alternatively, only one base table and its corresponding collection derived table are specified in the FROM clause.

3. If the GROUP BY clause is specified, all of the following conditions are met:

   • Global hash grouping is applied as the grouping method.

   • If a set function is specified, only one of the following is specified:

     • Set function COUNT(*)

     • Set function MIN

     • Set function MAX

     • Set function COUNT with ALL specified

     • Set function SUM with ALL specified

     • Set function AVG with ALL specified

4. If the GROUP BY clause is not specified, all of the following conditions are met:

   • At least one of the following set functions is specified:

     • Set function COUNT(*)

     • Set function MIN

     • Set function MAX

     • Set function COUNT with ALL specified

     • Set function SUM with ALL specified

     • Set function AVG with ALL specified

   • If an ORDER BY clause is specified and a value expression containing a set function is specified as the sort key, the value expression is specified in the selection expression.

5. If a HAVING clause is specified and a set function is specified in the search condition of the HAVING clause, only one of the following is specified:

   • Set function COUNT(*)

   • Set function MIN

   • Set function MAX

   • Set function COUNT with ALL specified

   • Set function SUM with ALL specified

   • Set function AVG with ALL specified

6. The value expression meets all of the following conditions:

   • No dynamic parameter is specified.

   • No datetime information acquisition function is specified.

   • No user information acquisition function is specified.

   • No scalar function is specified.

     • RANDOM

     • RANDOM_NORMAL

     • RANDOMCURSOR

     • RANDOMROW

   • The scalar function CONTAINS for which a synonym-search specification is specified is not specified.

   • No window function is specified.

   • A value expression where the result is of the STRING type is not specified.

Important

• Even if all of the above conditions are met, if the HADB server determines that the SQL parallel execution function cannot be effectively utilized, the SQL parallel execution function will not be applied to retrieval SQL statements.

• The SQL parallel execution function is not applied to SQL statements specified in the SQL statement file of the adbexport command.

To Page Top