2.24.2 Join method SQL optimization specification

Organization of this subsection
(1) Function
(2) Format
(3) Operands
(4) Rules
(5) Notes

(1) Function

The join method SQL optimization specification allows you to specify a join method for a joined table.

(2) Format

Join-method-SQL-optimization-specification::= BY {NEST|HASH|MERGE}

(3) Operands

NEST
This option can be specified to make the join method into either a nest-loop-join or a distributed nest-loop-join.
The result is a nest-loop-join if the inner table is a base table or a named derived table based on the base table; it is a distributed nest-loop-join if the inner table is an outer table or a named derived table based on the outer table.
HASH
This option can be specified when making the join method into a hash-join.
MERGE
This option can be specified when making the join method into a merge-join.

(4) Rules

  1. When specified for a join that is executed on a foreign server, the SQL optimization specification is nullified.
  2. Specifying HASH requires the same preparations as specifying execution of a hash-join or a subquery hash for the SQL extension optimizing option. For preparations for applying either hash-join or subquery-hash-join, see the HiRDB Version 8 UAP Development Guide.

(5) Notes

  1. For join methods, see the HiRDB Version 8 UAP Development Guide. Join methods can be checked by using the access path display utility.