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-3 lists and describes the SQL optimization specification facilities, Table 5-4 lists and describes the SQL optimization option facilities, and Table 5-5 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. For details about the SQL optimization specifications, SQL optimization options, and SQL extension optimizing options, see the HiRDB Version 9 UAP Development Guide.

Table 5-3 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 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-4 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.
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.
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.
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.
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.
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.
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.
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 (such as in 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.
Rapid grouping facility	Uses hashing to rapidly process the grouping specified by the GROUP BY clause of an SQL.
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.
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.
Suppressing index use (forced table scan)	Normally, HiRDB determines whether indexes are 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.
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.
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 indexes are being used can be determined from the access path display utility.
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 might be used. In general, we recommend that you avoid specifying this optimization option. Instead, specify rapid search conditions directly in an SQL statement.
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.
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.
Facility for moving search conditions into derived tables	Normally, a work table for a derived table also stores rows that do not match the search condition for the derived table's columns. By applying this optimization option, HiRDB creates a work table for the derived table after removing the rows that do not match the search condition for the derived table's columns. As a result, the size of the work table and the number of input/output operations involving the work table can be reduced. Furthermore, using such an access path sometimes makes it possible to use an index that can more effectively narrow the rows to be searched. We recommend that you use this function when you install HiRDB Version 08-02 or newer for the first time.

Table 5-5 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.
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. Determine whether hash-execution of a hash join or a subquery is to be applied by taking into consideration the join method and external reference.
Facility for applying join conditions that include value expressions	Creates a join condition for conditions that include value expressions.
Applying search conditions that include scalar operations to an index search	Improves execution performance of scalar operations that can be evaluated before a search by using an index to search with search conditions that are included in condition values for columns.
Enabling the substructure index to the XMLEXISTS predicate, including the parameters	Makes a substructure index usable when the following two conditions are satisfied: CAST specification is specified for the XML query variable of the XMLEXISTS predicate, and the ? parameter, an SQL parameter, or an SQL variable is specified for the value expression of the CAST specification. The substructure specification of the defined index matches the substructure path specified as a condition in the XML query of the XMLEXISTS predicate.
Applying merge to derived tables in a FROM clause	HiRDB does not need to create a work table when it executes an SQL statement that specifies only simple derived tables in the FROM clause, such as row selections or column projections. This can improve the execution performance of SQL statements that specify derived tables in the FROM clause. For the conditions under which HiRDB does not create a work table, see Rules on derived tables in a FROM clause under Table reference in the manual HiRDB Version 9 SQL Reference.
Facility for converting outer joins to inner joins	Converts the outer join to an inner join in cases where an outer join was specified for a query, and it is obvious that the same results would be obtained if an inner join were specified. This optimization can be expected to improve execution performance, not only by avoiding unnecessary processing, but also by making it possible to apply an index to the search conditions for the columns of the internal table specified in the WHERE clause.

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. Furthermore, 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.