Join-Method Directives

Use join-method directives to influence how tables are joined in a query.

Join-Method Directives:
 
                      .-,------------------------.
                      V                     (1)  |
|--+-+-USE_NL---+--(----| Table Reference |------+----------------------------------------+-->
   | '-AVOID_NL-'                                                                         |
   |                    .-,-------------------------------------------------------------. |
   |                    V                     (1)                                       | |
   '-+-AVOID_HASH-+--(----| Table Reference |-------+---------------------------------+-+-'
     '-USE_HASH---'                                 '-+-/BUILD-+--+-----------------+-'
                                                      '-/PROBE-'  |            (2)  |
                                                                  '-/BROADCAST------'
 
>--)--+----------+----------------------------------------------|
      '-comments-'
 

1. See Access-Method Directives
2. Extended Parallel Server only

This diagram is simplified: /BROADCAST is not valid with AVOID_HASH.

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.

• /BROADCAST

  Only Extended Parallel Server supports the BROADCAST directive, which can improve performance when small base tables or derived tables with few records (< 100K in data size) are involved in a join. In such cases, all the records of the small tables are sent to each instance of a given join operation. The BROADCAST directive must also include either /BUILD or /PROBE, but it is not valid with AVOID_HASH. If the /BUILD option is included, the USE_HASH table is broadcast. If the /PROBE option is included, the join in which the USE_HASH table participates is broadcast.

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;