SQL optimization option facility | Identifier | Unsigned integer | S | P |
---|---|---|---|---|
Forced nest-loop-join | "FORCE_NEST_JOIN" | 4 | Y | Y |
Making multiple SQL objects | "SELECT_APSL" | 10 | Y | Y |
Increasing the target floatable servers (back-end servers for fetching data) | "FLTS_INC_DATA_BES" | 16 | N | Y |
Prioritized nest-loop-join | "PRIOR_NEST_JOIN" | 32 | Y | Y |
Increasing the number of floatable server candidates | "FLTS_MAX_NUMBER" | 64 | N | Y |
Priority of OR multiple index use | "PRIOR_OR_INDEXES" | 128 | Y | Y |
Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server | "SORT_DATA_BES" | 256 | N | Y |
Suppressing use of AND multiple indexes | "DETER_AND_INDEXES" | 512 | Y | Y |
Rapid grouping facility | "RAPID_GROUPING" | 1024 | Y | Y |
Limiting the target floatable servers (back-end servers for fetching data) | "FLTS_ONLY_DATA_BES" | 2048 | N | Y |
Separating data collecting servers | "FLTS_SEPARATE_COLLECT_SVR" | 2064 | N | Y |
Suppressing index use (forced table scan) | "FORCE_TABLE_SCAN" | 4096 | Y | Y |
Forcing use of multiple indexes | "FORCE_PLURAL_INDEXES" | 32768 | Y | Y |
Suppressing creation of update-SQL work tables | "DETER_WORK_TABLE_FOR_UPDATE" | 131072 | Y | Y |
Deriving search acceleration condition | "DERIVATIVE_COND" | 262144 | Y | Y |
Applying key condition that includes scalar operation | "APPLY_ENHANCED_KEY_COND" | 524288 | Y | Y |
Facility for batch acquisition from functions provided by plug-ins | "PICKUP_MULTIPLE_ROWS_PLUGIN" | 1048576 | Y | Y |
SQL extension optimizing option facility | Identifier | Unsigned integer |
---|---|---|
Application of optimizing mode 2 based on cost | "COST_BASE_2" | 1 |
Hash execution of a hash join or a subquery1 | "APPLY_HASH_JOIN" | 2 |
Deterring of foreign server execution in SQL statement containing join1, 2 | "DETER_JOIN_SQL" | 67108864 |
Forcing of foreign server execution in SQL statement containing direct product1, 2 | "FORCE_CROSS_JOIN_SQL" | 134217728 |
Deterring of derivation of search acceleration condition that is generated unconditionally and that can be executed in foreign server1, 2 | "DETER_FSVR_DERIVATIVE_COND" | 1073741824 |
Work table processing classification | Variable name | Value | Processing method of HiRDB |
---|---|---|---|
Lock acquisition method when AND multiple indexes are used1 | a | 0 | When the use of AND multiple indexes is selected as the access path, the following occurs: If any of the predicates (data) containing the columns for which the indexes to be used for search are defined satisfies the search condition, that data is locked in the shared mode (PR). Consequently, even if WITH EXCLUSIVE LOCK is specified as the lock option2, a lock is applied in the shared mode (PR), and not in the exclusive mode (EX). |
1 | When the use of AND multiple indexes is selected as the access path, the following occurs: If any of the predicates (data) containing the columns for which the indexes to be used for search are defined satisfies the search condition, that data is locked in the specified mode. However, until the search for the first piece of data is completed, a predicate is logically computed for the column for which the index used for the search is defined, and the lock is released from any data that does not satisfy the search condition. | ||
Suppression of message output during automatic extension of the work table buffer3 | b | 0 | During automatic extension of the work table buffer, the KFPH29008-I message is output. This message is output during the first automatic extension of the work table buffer of each server process. |
8 | During automatic extension of the work table buffer, the KFPH29008-I message is not output. |
Work table processing classification | Value | Specification guidelines |
---|---|---|
Lock acquisition method when AND multiple indexes are used | 0 | When 0 is specified, no lock release is performed, and thus the SQL processing time decreases accordingly. However, if multiple users simultaneously try to update the same table using AND multiple indexes, deadlock may occur. To avoid deadlock, specify 1. |
1 | When 1 is specified, lock release is performed, and thus the SQL processing time increases accordingly. However, because the ultimate lock range becomes narrower, the concurrent executability of update-UAPs improves. | |
Suppression of extension allocation message for work table buffer | 0 | When 0 is specified, you can monitor whether extension has been allocated for the work table buffer. |
8 | By specifying 8, you can reduce the messages output volume. |