6.2.2 Operands related to SQL optimization

3) 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 facilityIdentifierUnsigned integerSP
Forced nest-loop-join"FORCE_NEST_JOIN"4YY
Making multiple SQL objects"SELECT_APSL"10YY
Increasing the target floatable servers (back-end servers for fetching data)"FLTS_INC_DATA_BES"16NY
Prioritized nest-loop-join"PRIOR_NEST_JOIN"32YY
Increasing the number of floatable server candidates"FLTS_MAX_NUMBER"64NY
Priority of OR multiple index use"PRIOR_OR_INDEXES"128YY
Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server"SORT_DATA_BES"256NY
Suppressing use of AND multiple indexes"DETER_AND_INDEXES"512YY
Rapid grouping facility"RAPID_GROUPING"1024YY
Limiting the target floatable servers (back-end servers for fetching data)"FLTS_ONLY_DATA_BES"2048NY
Separating data collecting servers"FLTS_SEPARATE_COLLECT_SVR"2064NY
Suppressing index use (forced table scan)"FORCE_TABLE_SCAN"4096YY
Forcing use of multiple indexes"FORCE_PLURAL_INDEXES"32768YY
Suppressing creation of update-SQL work tables"DETER_WORK_TABLE_FOR_UPDATE"131072YY
Deriving search acceleration condition"DERIVATIVE_COND"262144YY
Applying key condition that includes scalar operation"APPLY_ENHANCED_KEY_COND"524288YY
Facility for batch acquisition from functions provided by plug-ins"PICKUP_MULTIPLE_ROWS_PLUGIN"1048576​YY
Facility for moving search conditions into a derived table"MOVE_UP_DERIVED_COND"2097152​YY
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 earlier version
    The total value specified in HiRDB Version 5.0 or 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 = 14,128
    However, because this specification makes it difficult to identify the facilities being applied, use of identifier specification is recommended.
Operand rules
  • Identifiers and unsigned integers cannot be specified together.
  • Identifier specification
    [Figure]Enclose each SQL optimization option in quotation marks (").
    [Figure]If no SQL optimization option is used, specify NONE. When NONE and an identifier are both specified, the specification of NONE is invalid.
    [Figure]Identifiers can be specified in uppercase or lowercase letters.
    [Figure]Specifying the same identifier more than once is the same as specifying it once.
  • Unsigned integer specification
    [Figure]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.
    [Figure]Specifying the same unsigned integer more than once is the same as specifying it once.
Specification guidelines
For specification guidelines, see PDSQLOPTLVL in the HiRDB Version 9 UAP Development Guide.
Operand default
If this operand is omitted, the specification of the same operand in the system common definition is assumed. If the same operand is also omitted in the system common definition, 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"
However, if v6compatible has been specified in the pd_sysdef_default_option operand, the default is SELECT_APSL.
Notes
  • If SQL optimization is specified in an SQL statement, 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 SQL optimization 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 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.
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.
4) pd_additional_optimize_level = SQL-extension-optimizing-option [,SQL-extension-optimizing-option]...
~<identifier or unsigned integer>
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 facilityIdentifierUnsigned 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
    [Figure]Enclose each SQL extension optimizing option in quotation marks (").
    [Figure]If you do not use the SQL extension optimizing option explained here, specify NONE. However, if both NONE and an identifier other than NONE are specified, the specification of NONE is invalid.
    [Figure]Identifiers can be specified in uppercase or lowercase letters.
    [Figure]Specifying the same identifier more than once is the same as specifying it once.
  • Unsigned integer specification
    [Figure]If you do not use the SQL extension optimizing option explained here, specify 0. However, if both 0 and a non-zero unsigned integer are specified, the specification of 0 is invalid.
    [Figure]Specifying the same unsigned integer more than once is the same as specifying it once.
Specification guidelines
For specification guidelines, see PDADDITIONALOPTLVL in the HiRDB Version 9 UAP Development Guide.
Operand default
If this operand is omitted, the specification of the same operand in the system common definition is assumed. If the same operand is also omitted in the system common definition, the default is COST_BASE_2, unless v6compatible has been specified in the pd_sysdef_default_option operand, in which case the default is NONE.
Notes
  • If the SQL optimization is specified in an SQL statement, SQL optimization specification takes precedence over the specification in this operand. For details about SQL optimization specification, see the 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.
5) pd_floatable_bes = "back-end-server-name"[,"back-end-server-name"]...
~<identifier>((1-8 characters))
Specifies back-end servers to be used as floating servers.
Not all of the specified back-end servers can actually be used as floating servers.
Normally, servers used for data extraction are not used as floating servers. However, this operand enables servers that are used for data extraction to also become floating server candidates.
Operand rules
  • If an undefined back-end server name is specified, it is ignored.
  • If all the specified back-end server names are undefined, this entire operand is ignored.
Relationship to other operands
  • If the pd_non_floatable_bes operand is also specified, it is invalidated.
  • If the pd_floatable_bes operand is specified, increasing the target floatable servers (back-end servers for fetching data) of the pd_optimize_level operand is not applied.
  • If the pd_floatable_bes operand is specified, limiting the target floatable servers (back-end servers for fetching data) of the pd_optimize_level operand is not applied.
6) pd_non_floatable_bes = "back-end-server-name"[,"back-end-server-name"]...
~<identifier>((1-8 characters))
Specifies back-end servers that are not to be used as floating servers.
If all back-end servers are specified, this operand is invalid.
Operand rule
If an undefined back-end server name is specified, it is ignored.
Relationship to other operands
  • If the pd_floatable_bes operand is also specified, it is invalidated.
  • If the pd_non_floatable_bes operand is specified, increasing the target floatable servers (back-end servers for fetching data) of the pd_optimize_level operand is not applied.
  • If the pd_non_floatable_bes operand is specified, limiting the target floatable servers (back-end servers for fetching data) of the pd_optimize_level operand is not applied.