2.24 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
- 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 9 Command Reference.
- 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.
- The SQL optimization specification has priority over the SQL optimization option and SQL extension optimizing option. For details about the SQL optimization option and SQL extension optimizing option, see the ALTER PROCEDURE (Re-create SQL object of procedure), ALTER ROUTINE (Re-create SQL objects for functions, procedures, and triggers), ALTER TRIGGER (Re-create a trigger SQL object), CREATE [PUBLIC] PROCEDURE (Define procedure, define public procedure), or CREATE TRIGGER (Define a trigger) section in Chapter 3.
- Notes
- 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.
- 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