Join-Method Directives

Use join-method directives to influence how tables are joined in the HCL OneDB™-extension joined query.

(1)
Join-Method Directives

1 
2.1 USE_NL
2.1 AVOID_NL
1 (
1 + , %Table Reference1
1 
2.1 AVOID_HASH
2.1 USE_HASH
1 (
1 + ,  %Table Reference1? 
2.1 /BUILD
2.1 /PROBE
2  )
2?  comments
Notes:
Element Description Restrictions Syntax
comments Text to documents the directive Must appear between comment symbols Character string

Use commas or blank spaces to separate the elements within the parentheses.

The following table describes each of the join-method directives.

Keyword
Effect
USE_NL
Uses the specified tables as the inner table in a nested-loop join

If n tables are specified in the FROM clause, then at most (n-1) tables can be specified in the USE_NL join-method directive.

USE_HASH
Uses a hash join to access the specified table

You can also choose whether the table will be used to create the hash table or to probe the hash table.

AVOID_NL
Does not use the specified table as inner table in a nested loop join

A table listed with this directive can still participate in a nested loop join as the outer table.

AVOID_HASH
Does not access the specified table using a hash join

You can optionally use a hash join, but impose restrictions on the role of the table within the hash join.

A join-method directive takes precedence over the join method forced by the OPTCOMPIND configuration parameter.

When you specify the USE_HASH or AVOID_HASH directives (to use or avoid a hash join, respectively), you can also specify the role of each table:
  • /BUILD

    With the USE_HASH directive, this keyword indicates that the specified table be used to construct a hash table. With the AVOID_HASH directive, this keyword indicates that the specified table not be used to construct a hash table.

  • /PROBE

    With the USE_HASH directive, this keyword indicates that the specified table be used to probe the hash table. With the AVOID_HASH directive, this keyword indicates that the specified table not be used to probe the hash table. You can specify multiple probe tables as long as there is at least one table for which you do not specify PROBE.

For the optimizer to find an efficient join query plan, you must at least run UPDATE STATISTICS LOW for every table that is involved in the join, so as to provide appropriate cost estimates. Otherwise, the optimizer might choose to broadcast the entire table to all instances, even if the table is large.

If neither the /BUILD nor the /PROBE keyword is specified, the optimizer uses cost estimates to determine the role of the table.

In this example, the USE_HASH directive forces the optimizer to construct a hash table on the dept table and consider only the hash table to join dept with the other tables. Because no other directives are specified, the optimizer can choose the least expensive join methods for the other joins in the query. 
SELECT /*+ USE_HASH (dept /BUILD) 
   The optimizer must use dept to construct a hash table */
   name, title, salary, dname
   FROM emp, dept, job WHERE loc = 'Phoenix' 
      AND emp.dno = dept.dno  AND emp.job = job.job;

Join-method optimizer directives that you specify for an ANSI-compliant joined query are ignored, but they are listed under Directives Not Followed in the explain output file.