SQL optimization specification

Function
This function allows you to specify optimization for improving the retrieval efficiency of an SQL statement in the SQL statement.
SQL optimization can be specified for the following items:
- Used indexes
- Join methods
- Subquery execution methods
Common rules
1. In some cases, SQL optimization, when specified, may not take effect. You can check whether the SQL optimization specification has taken effect by using the access path display utility. For details about this utility, see the manual HiRDB Version 8 Command Reference.
2. You can enclose an SQL optimization specification by placing the /*>> and <<*/ symbols before and after it. Each SQL optimization specification must be enclosed separately. An SQL optimization specification enclosed in /*>> and <<*/ is not interpreted as a comment. Specifying an SQL optimization by enclosing it in /*>> and <<*/ can be useful for ensuring compatibility with APs that are common to other DBMSs.
3. The SQL optimization specification takes precedence over the SQL optimization specification option and the SQL extension optimizing option. For details about the SQL optimization specification option and the SQL extension optimizing option, see ALTER PROCEDURE, ALTER ROUTINE, ALTER TRIGGER, CREATE PROCEDURE, or CREATE TRIGGER.
Notes
1. If BY NEST is specified in a join method SQL optimization specification, and if an index is specified that cannot be used in a nest-loop join as an SQL optimization specification in a used index in the joined inner table, or the suppression of the use of the index is specified, the SQL optimization specification for the used index is nullified.
2. An SQL optimization specification enclosed in /*>> and <<*/ cannot be enclosed again in /*>> and <<*/.

Organization of this section: 2.24.1 SQL optimization specification for a used index; 2.24.2 Join method SQL optimization specification; 2.24.3 Subquery execution method SQL optimization specification; 2.24.4 Examples of SQL optimization specification

2.24 SQL optimization specification