Join-method directives

The join-method directives influence how the database server joins two tables in a query.

The following directives influence the join method between two tables:

USE_NL
Use the listed tables as the inner table in a nested-loop join.
USE_HASH
Access the listed tables with a hash join. You can also choose whether the table is used to create the hash table or to probe the hash table.
AVOID_NL
Do not use the listed tables as the inner table in a nested-loop join. A table listed with this directive can still participate in a nested-loop join as an outer table.
AVOID_HASH
Do not access the listed tables with a hash join. Optionally, you can allow a hash join but restrict the table from being the one that is probed or the table from which the hash table is built.

You can specify the keyword /BUILD after the name of a table in a USE_HASH or AVOID_HASH optimizer directives:

With USE_HASH directives, the /BUILD keyword tells the optimizer to use the specified table to build the hash table.
With AVOID_HASH, the /BUILD keyword tells the optimizer to avoid using the specified table to build the hash table.

You can specify the keyword /PROBE after the name of a table in a USE_HASH or AVOID_HASH optimizer directives:

With USE_HASH directives, the /PROBE keyword tells the optimizer to use the specified table to probe the hash table.
With AVOID_HASH directives, the /PROBE keyword tells the optimizer to avoid using the specified table to probe the hash table.