Updated on 2025-09-18 GMT+08:00

Join Operation Hints

Function

Specifies the join method. It can be nested loop join, hash join, or merge join.

Syntax

1
[no] nestloop|hashjoin|mergejoin([@block_name] table_list)

Parameter Description

  • no indicates that the specified hint will not be used for a join.
  • block_name indicates the block name of the statement block. For details, see block_name.
  • table_list is a string representing the hint table collection. The tables within this string match those in join_table_list, with the exception that parentheses for specifying join priorities are not allowed.

For example:

no nestloop(t1 t2 t3): nestloop is not used for joining t1, t2, and t3. The three tables may be joined in either of the two ways: Join t2 and t3, and then t1; join t1 and t2, and then t3. This hint takes effect only for the last join. If necessary, you can hint other joins. For example, you can add no nestloop(t2 t3) to join t2 and t3 first and to forbid the use of nestloop.

Examples

Hint the query plan in Examples as follows:

1
2
explain
select /*+ nestloop(store_sales store_returns item) */ i_product_name product_name ...

nestloop is used for the last join between store_sales, store_returns, and item. The optimized plan is as follows: