Taking into account the database status to determine the most efficient access paths is called SQL optimization. SQL optimization includes the SQL optimization specifications, the SQL optimization options, and the SQL extension optimizing options. Table 5-2 lists and describes the SQL optimization specification facilities, Table 5-3 lists and describes the SQL optimization option facilities, and Table 5-4 lists and describes the SQL extension optimizing option facilities. Note, however, that there are additional SQL optimization facilities that are not described here. Because these facilities invariably improve performance, they are not provided as options, but are always used.

Table 5-2 SQL optimization specification facilities

SQL optimization specification facility	Description
SQL optimization specification for index utilization	Enables you to use table searches to specify which index to utilize. It also enables you to use table searches to specify not to utilize an index (by using a table scan). You can use the following SQL methods to specify an SQL optimization specification for index utilization: Table expressions DELETE statement UPDATE statement, format 1
SQL optimization specification for the join method	Enables you to specify a join method (nest-loop-join, hash join, or merge join) for join tables. You can use the following SQL method to specify an SQL optimization specification for the join method: Table expressions
SQL optimization specification for the subquery execution method	Enables you to specify whether or not to set hash execution as the method of executing subqueries in predicates. You can use the following SQL method to specify the SQL optimization specification for the subquery execution method: Subqueries

Table 5-3 Facilities of SQL optimization options

SQL optimization option facility	Explanation
Forced nest-loop-join	If an index is defined in the joining condition column, only nest loop join is used for joining. For details about the nest-loop-joining method, see the HiRDB Version 8 UAP Development Guide.
Creating multiple SQL objects	Creates multiple SQL objects in advance and selects the optimal SQL object based on an embedded variable or ? parameter value during execution. For details about the creating multiple SQL objects, see the HiRDB Version 8 UAP Development Guide.
Increasing the target floatable servers (back-end servers for fetching data)	Normally, a back-end server not used for fetching data is used as a floating server. When this optimization method is used, a back-end server used for fetching data is also used as a floating server. However, because the number of back-end servers that can be used as floating servers is computed by HiRDB, not all back-end servers can be used as floating servers. To use all back-end servers, specify both this facility and the increasing the number of floatable server candidates facility. For details about floating server allocation, see the HiRDB Version 8 UAP Development Guide.
Prioritized nest-loop-join	If an index is defined for the joining condition column, nest-loop-join is used for the joining process with a higher priority. For details about the nest-loop-join method, see the HiRDB Version 8 Command Reference. For details about the nest-loop-joining method, see the HiRDB Version 8 UAP Development Guide.
Increasing the number of floatable server candidates	Normally, the needed number of floating servers is computed by HiRDB from the available floating servers and allocated. When this optimization method is applied, all available floating servers are used. However, back-end servers used for fetching data cannot be used as floating servers. To also use the back-end servers used for fetching data as floating servers, specify this facility together with the increasing the target floatable servers (back-end servers for fetching data) facility. For details about floating server allocation, see the HiRDB Version 8 UAP Development Guide.
Priority of OR multiple index use	Specify this facility to use a retrieval method that uses OR multiple indexes with a higher priority. OR multiple index use is a method that evaluates a search condition by using an index to retrieve each condition from multiple conditions joined with ORs in a search condition and obtains a sum set of the retrieval results. For details about the priority of OR multiple index use, see the HiRDB Version 8 UAP Development Guide.
Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server	Specify this facility to perform group processing, ORDER BY processing, and DISTINCT set function processing at the back-end server that defines the table (local back-end server), without having to use a floating server. For details about the group processing method, see the HiRDB Version 8 UAP Development Guide.
Suppressing use of AND multiple indexes	Ensures that an access path using AND multiple indexes is never used. When the search condition contains multiple conditions linked with AND and different indexes are defined for the individual columns (e.g., SELECT ROW FROM T1 WHERE C1=100 AND C2=200), use of AND multiple indexes means that individual indexes are used to create work tables for the rows that satisfy the condition and the product set of these rows is obtained. For details about the suppressing use of AND multiple indexes, see the HiRDB Version 8 UAP Development Guide.
Rapid grouping facility	Uses hashing to rapidly process the grouping specified by the GROUP BY clause of an SQL. For details about the grouping method, see the HiRDB Version 8 UAP Development Guide.
Limiting the target floatable servers (back-end servers for fetching data)	Normally, a back-end server not used for fetching data is used as a floating server. When this optimization method is used, only those back-end servers used for fetching data are used as floating servers. For details about floating server allocation, see the HiRDB Version 8 UAP Development Guide.
Separating data collecting servers	If increasing the target floatable servers (back-end servers for fetching data) and limiting the target floatable servers (back-end servers for fetching data) are both specified, they will function as the facility for separating data collecting servers. If the facility for separating data collecting servers is applied, a back-end server other than the data transfer source is allocated for data collection to an SQL that needs to collect data from multiple back-end servers into a single back-end server. Back-end servers other than those used for collecting data (including back-end servers for fetching data) are allocated as front-end servers for other purposes. For details about the floating server allocation method, see the HiRDB Version 8 UAP Development Guide.
Suppressing index use (forced table scan)	Normally, HiRDB determines whether indexes should be used. When this optimization method is applied, methods that do not use indexes are given higher priority. However, index use cannot be suppressed in the following cases: joining results in a nest-loop-join, a structured repetition predicate is specified as the search condition, or a condition includes the index type plug-in dedicated facility. For details about the suppressing index use, see the HiRDB Version 8 UAP Development Guide.
Forcing use of multiple indexes	Specify this optimization method to retrieve tables by forcibly selecting use of AND multiple indexes. When this optimization method is not specified and multiple conditions linked with AND have been specified, two indexes at most are used even if use of AND multiple indexes is selected. The number of indexes to be used will vary slightly depending on the table definition, index definition, and search condition. When this optimization is specified, all conditions that can narrow the search range using indexes will be used. When AND multiple indexes are used, search candidates can be narrowed down to a certain degree using these indexes. Moreover, AND multiple indexes are effective when there is little overlap in the product set. For details about the forcing use of multiple indexes, see the HiRDB Version 8 UAP Development Guide.
Suppressing creation of update-SQL work tables	If this optimization is specified when index key value no locking is being applied, HiRDB does not create work tables for internal processing even if an index is used for a retrieval with the FOR UPDATE clause specified, an UPDATE statement, or a DELETE statement. Therefore, SQL statements can be processed at high speed. Work tables will be created if the index key value no-locking option is not being applied. Whether or not indexes are being used can be determined from the access path display utility. For details about the suppressing creation of update-SQL work tables, see the HiRDB Version 8 UAP Development Guide.
Derivation of rapid search conditions	If this optimization facility is specified, rapid search conditions are derived. Rapid search conditions refer to conditions ranging from search conditions in the WHERE clause and ON search conditions in the FROM clause, to new conditions derived from CNF conversions or condition transitions. Deriving rapid search conditions improves search performance because it allows you to narrow down the rows that are searched at an early stage. However, generating and executing rapid search conditions takes time, or unexpected access paths may be used. In general, we recommend that you avoid specifying this optimization option. Instead, specify rapid search conditions directly in an SQL statement. For details about deriving rapid search conditions, see the HiRDB Version 8 UAP Development Guide.
Application of scalar operation-included key conditions	If this optimization facility is specified, within the limitations that specify scalar operations, if all columns included in the scalar operation are index configuration columns, searching is narrowed by evaluating these columns by one index key value at a time. These conditions are evaluated as key conditions. For details about applying key conditions included in scalar operations, see the HiRDB Version 8 UAP Development Guide.
Facility for batch acquisition from functions provided by plug-ins	If you specify a function provided by a plug-in as a search condition and HiRDB performs the search using a plug-in index, HiRDB normally acquires the results (row location information and the passed value if needed) returned from the function provided by the plug-in one row at a time. By applying this optimization, HiRDB can acquire in a batch multiple rows of results returned from the function provided by the plug-in, which reduces the number of times that HiRDB has to call the function. For details about the facility for batch acquisition from functions provided by plug-ins, see the HiRDB Version 8 UAP Development Guide.

Table 5-4 Facilities of SQL extension optimizing options

SQL extension optimizing option facility	Explanation
Application of optimizing mode 2 based on cost	Optimizes using optimizing mode 2 based on cost. For details about optimizing mode 2 based on cost, see the HiRDB Version 8 UAP Development Guide.
Hash-execution of a hash join or a subquery	Optimizes by applying hash join for a joined retrieval. If the retrieval is accompanied by a subquery, the subquery is processed by hashing. Whether or not hash-execution of a hash join or a subquery is to be applied should be decided by taking into consideration the join method and external reference. For details about join methods and external reference, see the HiRDB Version 8 UAP Development Guide.
Suppression of foreign server execution of SQL statements that include a join	Suppresses creation of an SQL statement containing a join when an SQL statement that accesses a foreign table would be created from a query containing an access to a foreign table. Instead of creating an SQL statement that includes a join, this facility creates an SQL statement that retrieves the data from the foreign table that would have been inserted by the join. Note that the join processing is performed by HiRDB. For details about suppression of foreign server execution of SQL statements that include a join, see HiRDB External Data Access Version 8.
Forced foreign server execution of SQL statements that include a direct product	Attempts to create an SQL statement that includes a direct product when an SQL statement that accesses a foreign table is created from a query containing an access to a foreign table. For details about forced foreign server execution of SQL statements that include a direct product, see HiRDB External Data Access Version 8.
Suppression of unconditionally generated derived rapid search conditions that can be executed on foreign servers	Enables you to suppress unconditionally derived rapid search conditions that can be executed on a foreign server. Generation and execution of derived rapid search conditions may take time, or an unexpected access path may be used. Specify this optimization facility for such cases. Note that, if derivation of rapid search conditions is specified as an SQL optimization option, this optimization facility is ignored, even if specified. For details about deriving rapid search conditions, see the HiRDB Version 8 UAP Development Guide.

Setting SQL optimization options and SQL extension optimizing options

The following methods can be used to specify SQL optimization options and SQL extension optimizing options:

1. Specifying the pd_optimize_level or pd_additional_optimize_level operand of the system common definition or front-end server definition
2. Specifying PDSQLOPTLVL and PDADDITIONALOPTLVL in the client environment definition
3. Specifying an SQL optimization option or SQL extension optimizing option in an SQL statement of a stored routine or trigger.

If more than one of these methods is specified concurrently, the order of precedence is 3, 2, 1. For details about SQL optimization options and SQL extension optimizing options, see the HiRDB Version 8 UAP Development Guide.

If you also specify an SQL optimization specification at the same time, the SQL optimization specification has precedence over any SQL optimization option or SQL extension optimizing option that has been specified. For details about the SQL optimization specifications, see the manual HiRDB Version 8 SQL Reference.