Operands related to SQL optimization

50) pd_optimize_level = SQL-optimization-option [,SQL-optimization-option]...

~<identifier or unsigned integer>

Specifies SQL optimization options. For details about the SQL optimization options, see the HiRDB Version 9 UAP Development Guide. The SQL optimization option facilities are explained as follows.

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
Facility for moving search conditions into a derived table	"MOVE_UP_DERIVED_COND"	2097152	Y	Y

S: HiRDB/Single Server

P: HiRDB/Parallel Server

Y: Specification is valid.

N: Specification is not applicable.

Operand specification methods

Select the SQL optimization options to be applied and specify their identifiers or unsigned integers. Although either identifiers or unsigned integers (computed values) can be used to specify options, the use of identifiers is usually recommended.

Using identifiers
To apply forced nest-loop-join and making multiple SQL objects, specify the following:
pd_optimize_level="FORCE_NEST_JOIN","SELECT_APSL"
Using unsigned integers
To apply forced nest-loop-join and making multiple SQL objects, specify the following:
pd_optimize_level=4,10
When you have upgraded from HiRDB Version 5.0 or an earlier version
The total value specified in HiRDB Version 5.0 or an earlier version is also valid. If there is no need to change the optimization option, you need not change the specification of this operand after upgrading to HiRDB Version 6 or a later version. To add the optimization option, use the following example.
Example:
Forced nest-loop-join and making multiple SQL objects have been applied to HiRDB Version 5.0, and priority of OR multiple index use is to be newly added.
pd_optimize_level = 14128
However, because this specification makes it difficult to identify the facilities being applied, changing to specification of identifiers is recommended.

Operand rules

Identifiers and unsigned integers cannot be specified together.
Identifier specification
Enclose each SQL optimization option in quotation marks (").
If no SQL optimization option is used, specify NONE. When NONE and an identifier are both specified, the specification of NONE is invalid.
Identifiers can be specified in uppercase or lowercase letters.
Specifying the same identifier more than once is the same as specifying it once.
Unsigned integer specification
If you do not use the SQL optimization option explained here, specify 0. However, if both 0 and a non-zero unsigned integer are specified, the specification of 0 is ignored.
Specifying the same unsigned integer more than once is the same as specifying it once.

Specification guidelines: For the specification guidelines, see PDSQLOPTLVL in the HiRDB Version 9 UAP Development Guide.

Operand default value

If this operand is omitted, the following values are assumed:

HiRDB/Single Server
"PRIOR_NEST_JOIN","PRIOR_OR_INDEXES","DETER_AND_INDEXES","RAPID_GROUPING","DETER_WORK_TABLE_FOR_UPDATE","APPLY_ENHANCED_KEY_COND"
HiRDB/Parallel Server
"PRIOR_NEST_JOIN","PRIOR_OR_INDEXES","SORT_DATA_BES","DETER_AND_INDEXES","RAPID_GROUPING","DETER_WORK_TABLE_FOR_UPDATE","APPLY_ENHANCED_KEY_COND"

When v6compatible is specified in the pd_sysdef_default_option operand, SELECT_APSL is assumed.

Notes

If the SQL optimization option is specified in an SQL statement, the SQL optimization specification takes precedence over the specification in this operand. For details about SQL optimization specification, see the manual HiRDB Version 9 SQL Reference.
If the SQL optimization option is specified in an SQL statement (CREATE PROCEDURE, CREATE TYPE, ALTER PROCEDURE, CREATE TRIGGER, ALTER ROUTINE, or ALTER TRIGGER) inside a stored routine or trigger, the SQL optimization option inside the SQL statement takes precedence over the specification in this operand.

Relationship to client environment definition: The value of this operand can be different for each client. To change the value for a client, specify the PDSQLOPTLVL operand in the client environment definition. For details about the PDSQLOPTLVL operand, see the HiRDB Version 9 UAP Development Guide.

Relationship to other operands

When the pd_floatable_bes or pd_non_floatable_bes operand is specified in the front-end server definition, specification of increasing the target floatable servers (back-end servers for fetching data) and limiting the target floatable servers (back-end servers for fetching data) is invalid.
When KEY is specified for the pd_indexlock_mode operand, specification of suppressing creation of update-SQL work tables is invalid.

51) pd_additional_optimize_level = SQL-extension-optimizing-option [,SQL-extension-optimizing-option]...

~<identifier or unsigned integer> <<'COST_BASE_2'>>

Specifies SQL extension optimizing options. For details about the SQL extension optimizing options, see the HiRDB Version 9 UAP Development Guide.

The following table describes the SQL extension optimizing option facilities.

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 subquery^#	"APPLY_HASH_JOIN"	2
Facility for applying join conditions that include value expressions^#	"APPLY_JOIN_COND_FOR_VALUE_EXP"	32
Enabling substructure indexes for XMLEXISTS predicates that include parameters^#	"ENABLE_INDEX_XMLEXISTS_PARAM"	256

#: These items are valid when application of optimizing mode 2 based on cost is specified.

Operand specification methods

Select the SQL extension optimization options to be applied and specify their identifiers or unsigned integers. Although either identifiers or unsigned integers (computed values) can be used to specify options, the use of identifiers is usually recommended.

Using identifiers
To apply application of optimizing mode 2 based on cost and hash execution of a hash join or a subquery, specify the following:
pd_additional_optimize_level= "COST_BASE_2", "APPLY_HASH_JOIN"
Using unsigned integers
To apply application of optimizing mode 2 based on cost and hash-execution of a hash join or a subquery, specify the following:
pd_additional_optimize_level=1,2

Operand rules

Identifiers and unsigned integers cannot be specified together.
Identifier specification
Enclose each SQL extension optimizing option in quotation marks (").
Specify NONE if the SQL extension optimizing option explained here is not used. However, if both NONE and an identifier other than NONE are specified, the specification of NONE is invalid.
Identifiers can be specified in uppercase or lowercase letters.
Specifying the same identifier more than once is the same as specifying it once.
Unsigned integer specification
Specify 0 if the SQL extension optimizing option explained here is not used. However, if both 0 and an unsigned non-zero integer are specified, the specification of 0 is invalid.
Specifying the same unsigned integer more than once is the same as specifying it once.

Specification guidelines: For the specification guidelines, see PDADDITIONALOPTLVL in the HiRDB Version 9 UAP Development Guide.

Notes

If SQL optimization is specified in an SQL statement, the SQL optimization specification takes precedence over the specification in this operand. For details about SQL optimization specification, see the manual HiRDB Version 9 SQL Reference.
If the SQL extension optimizing option is specified in an SQL statement (CREATE PROCEDURE, CREATE TYPE, CREATE TRIGGER, ALTER PROCEDURE, ALTER ROUTINE, or ALTER TRIGGER) inside a stored routine or trigger, the SQL extension optimizing option inside the SQL statement takes precedence over the specification in this operand.

Relationship to client environment definition: The value of this operand can be different for each client. To change the value for a client, specify the PDADDITIONALOPTLVL operand in the client environment definition. For details about the PDADDITIONALOPTLVL operand, see the HiRDB Version 9 UAP Development Guide.

Relationship to other operands: When v6compatible is specified in the pd_sysdef_default_option operand, the default for this operand is NONE.

52) pd_hashjoin_hashing_mode = TYPE1 | TYPE2

Specifies the hashing method to be used when hash join, subquery hash execution is specified for the SQL extension optimizing option.

TYPE1:: This hashing method is used in versions earlier than 07-02.

TYPE2:: Provides more uniform hashing than TYPE1.

Specification guidelines

Normally, specify TYPE2. However, uniform hashing might not occur depending on the data in the column specified for the join condition. In this case, specify TYPE1.
If specifying TYPE1 does not produce the expected performance in a user system that has been upgraded to 07-02 or a later version, specify TYPE2.

Relationship to client environment definition: The value of this operand can be changed for each client. To change the operand for a client, specify the PDHJHASHINGMODE operand in the client environment definition. For details about the PDHJHASHINGMODE operand, see the HiRDB Version 9 UAP Development Guide.

53) pd_hash_table_size = hash-table-size

~<unsigned integer><<256>> (kilobytes)

32-bit mode: ((128-524288))
64-bit mode: ((128-2097152))

Specifies the size of the hash table to be used when application of hash-execution of a hash join or a subquery is specified as an SQL optimization option.

Specification guidelines: For details about the hash table size to be specified in this operand, see the HiRDB Version 9 UAP Development Guide.

Operand rules: Specify this value as a multiple of 128. If a value that is not a multiple of 128 is specified, the specified value is rounded up automatically to the next multiple of 128.

Relationship to client environment definition: The value of this operand can be different for each client. To change the value for a client, specify the PDHASHTBLSIZE operand in the client environment definition. For details about the PDHASHTBLSIZE operand, see the HiRDB Version 9 UAP Development Guide.

Effects on individual estimation formulas

If the value of the pd_hash_table_size operand is changed, the following estimation formulas are affected:

HiRDB Version 9 Installation and Design Guide:

Procedure for obtaining the size of the memory required during hash join and subquery hash execution under Estimating the memory size required for a HiRDB/Single Server
Procedure for obtaining the size of the memory required during hash join and subquery hash execution under Estimating the memory size required for a HiRDB/Parallel Server

54) pd_work_table_option = work-table-processing-option

~<unsigned integer> <<1>>

Specifies the HiRDB processing method to be used for executing an SQL statement that uses a work table. Use the following formula to determine the value to be specified for this operand:

Work table processing option = a + b

Work table processing classification	Variable name	Value	Processing method of HiRDB
Lock acquisition method when AND multiple indexes are used^#¹	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 option^#², a lock is applied in the shared mode (PR), and not in the exclusive mode (EX).
	a	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 buffer^#³	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.
	b	8	During automatic extension of the work table buffer, the KFPH29008-I message is not output.

#1: Specifies the HiRDB processing method to be used when using AND multiple indexes.; Use of AND multiple indexes means the following: When a search condition contains multiple conditions connected using AND, and different indexes are defined for each column (for example, SELECT ROW FROM T1 WHERE C1 = 100 AND C2 = 200), the multiple indexes are used to create work tables of rows that satisfy the conditions and obtain the product set of these tables.

#2

If no lock option is specified for an SQL statement, WITH SHARE LOCK is normally assumed. However, note that the lock option to be assumed differs in the following cases:

The FOR UPDATE clause is specified for the cursor.
The PDISLLVL operand is specified in the client environment definition.
A data guarantee level is specified for a procedure, facility, or CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE, ALTER PROCEDURE, ALTER ROUTINE, or ALTER TRIGGER trigger.

#3

As a precondition, the pd_work_buff_expand_limit operand must be specified. For automatic extension of the work table buffer, see the description of this operand.

Specification guidelines

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 might occur. To avoid deadlock, specify 1.
Lock acquisition method when AND multiple indexes are used	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.

Notes

Note the following when you specify a lock acquisition mode using AND multiple indexes for work table processing classification:

The lock acquisition mode using AND multiple indexes that is applied during stored routine and trigger generation (execution of CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE, ALTER PROCEDURE, ALTER ROUTINE, or ALTER TRIGGER) is applied to procedures, facilities, and triggers. The lock acquisition mode using AND multiple indexes that is applied during the execution of a stored routine (execution of a CALL statement) or trigger is not applied.
If AND multiple indexes are suppressed by the following operands or options, the specification of the pd_work_table_option operand is invalid.
pd_optimize_level operand
PDSQLOPTLVL operand in the client environment definition
SQL optimization option of CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE, ALTER PROCEDURE, ALTER ROUTINE, or ALTER TRIGGER

Relationship to other operands: When v6compatible is specified in the pd_sysdef_default_option operand, the default for this operand is 0.

55) pd_apply_search_ats_num = maximum-number-of-combinations-of-narrowing-values-applied-to-ATS-search-condition

~<unsigned integer>((255-30000))<<255>>

Specifies for an indexed search a maximum number of combinations of narrowing values that can be applied to the ATS or RANGES search condition when an IN predicate or quantified predicate (= ANY (table subquery) or = SOME (table subquery)) is specified.

Specification guidelines

For initial installation of HiRDB version 08-04 or later:
We recommend that you specify the value 30000.
For upgrading of HiRDB version 08-03 or earlier:
We recommend that you specify 255 so that you can use the system under the same conditions as before upgrading.
However, when an IN predicate or quantified predicate (= ANY (table subquery) or = SOME (table subquery)) has been specified in the RANGE or RANGES SQL statement and the number of combinations of search condition narrowing values exceeds 255, performance might be improved by modifying the value specified for this option. For details about the types of search conditions, see the manual HiRDB Version 9 Command Reference.

Note: When the specified value is exceeded, the value is modified so that the search condition becomes different internally, and then the SQL is executed. For this reason, the access path might be other than what was anticipated.

2.2.9 Operands related to SQL optimization