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.