All Products
Search
Document Center

Hints related to join operations

Last Updated: Jun 18, 2021

USE_MERGE Hint

The USE_MERGE hint instructs the optimizer to join each specified table with another row resource by using a sort-merge join. We recommend that you use the USE_NL and USE_MERG hints when you use the LEADING and ORDERED hints. The optimizer uses these hints if the referenced table is the internal table of a join operation. The hints are ignored if the referenced table is an external table.

The USE_MERGE hint uses the following syntax:

/*+ USE_MERGE ( [ @ queryblock ] tablespec [ tablespec ]... ) */

The USE_MERGE hint uses the MERGE-JOIN algorithm if a table is specified as an internal table. ApsaraDB for OceanBase allows you to use the MERGE-JOIN algorithm only if a join condition is used to specify the equivalence relation between two fields in the two tables. If you attempt to join two tables without such join conditions, the USE_MERGE hint is invalid.

The following statement provides an example of the USE_MERGE hint:

SELECT /*+ USE_MERGE(employees departments) */ * 
  FROM employees, departments 
  WHERE employees.department_id = departments.department_id;

NO_USE_MERGE Hint

The NO_USE_MERGE hint instructs the optimizer to exclude the joins specified in the USE_MERGE hint when the optimizer uses a specified table as an internal table and joins the specified table with another row resource.

The NO_USE_MERGE hint uses the following syntax:

/*+ NO_USE_MERGE ( [ @ queryblock ] tablespec [ tablespec ]... ) */

The following statement provides an example of the NO_USE_MERGE hint:

SELECT /*+ NO_USE_MERGE(e d) */ *
  FROM employees e, departments d
  WHERE e.department_id = d.department_id;

USE_HASH Hint

The USE_HASH hint instructs the optimizer to join each specified table with another row resource by using the HASH-JOIN algorithm.

The USE_HASH hint uses the following syntax:

/*+ USE_HASH ( [ @ queryblock ] tablespec [ tablespec ]... ) */

The following statement provides an example of the USE_HASH hint:

SELECT /*+ USE_HASH(l h) */ *
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id
    AND l.order_id > 2400;

NO_USE_HASH Hint

The NO_USE_HASH hint instructs the optimizer to exclude the joins specified in the USE_HASH hint when the optimizer uses a specified table as an internal table and joins the specified table to another row resource.

The NO_USE_HASH hint uses the following syntax:

/*+ NO_USE_HASH ( [ @ queryblock ] tablespec [ tablespec ]... ) */

The following statement provides an example of the NO_USE_HASH hint:

SELECT /*+ NO_USE_HASH(e d) */ *
  FROM employees e, departments d
  WHERE e.department_id = d.department_id;

USE_NL Hint

The USE_NL hint instructs the optimizer to join each specified table to another row resource by using a nested loop join. This hint also instructs the optimizer to use the specified table as an internal table by using the NL-JOIN algorithm. We recommend that you use the USE_NL and USE_MERG hints together with the LEADING and ORDERED hints. The optimizer uses these hints when the referenced table is the internal table of a join. The hints are ignored if the referenced table is an external table.

The USE_NL hint uses the following syntax:

/*+ USE_NL ( [ @ queryblock ] tablespec [ tablespec ]... ) */

In the following example, a hint is used to forcibly execute a nested loop and access orders through a full table scan. The filter condition l.order_id = h.order_id is applied to every row. If a row meets the filter condition, order_items is queried through the index order_id:

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id;

NO_USE_NL Hint

The NO_USE_NL hint instructs the optimizer to exclude nested loop joins when the optimizer uses a specified table as an internal table and joins the specified table to another row resource.

The NO_USE_NL hint uses the following syntax:

/*+ NO_USE_NL ( [ @ queryblock ] tablespec [ tablespec ]... ) */

The following statement provides an example of the NO_USE_NL hint:

SELECT /*+ NO_USE_NL(e d) */ *
  FROM employees e, departments d
  WHERE e.department_id = d.department_id;

USE_BNL Hint

The USE_BNL hint instructs the optimizer to join each specified table to another row resource by using a block-nested loop join. The hint also instructs the optimizer to use the specified table as an internal table by using the BNL-JOIN algorithm. We recommend that you use the USE_BN hint together with the LEADING and ORDERED hints. The optimizer uses these hints when the referenced table is the internal table of a join. The hints are ignored if the referenced table is an external table.

The USE_BNL hint uses the following syntax:

/*+ USE_BNL ( [ @ queryblock ] tablespec [ tablespec ]... ) */

In the following example, a hint is used to forcibly execute a block nested loop and access orders through a full table scan. The filter condition l.order_id = h.order_id is applied to every row. If a row meets the filter condition, order_items is queried through the index order_id:

SELECT /*+ USE_BNL(l h) */ h.customer_id, l.unit_price * l.quantity
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id;

NO_USE_BNL Hint

The NO_USE_BNL hint instructs the optimizer to exclude the joins specified in the USE_BNL hint when the optimizer uses a specified table as an internal table and joins the specified table to another row resource.

The NO_USE_BNL hint uses the following syntax:

/*+ NO_USE_BNL ( [ @ queryblock ] tablespec [ tablespec ]... ) */

The following statement provides an example of the NO_USE_BNL hint:

SELECT /*+ NO_USE_BNL(e d) */ *
  FROM employees e, departments d
  WHERE e.department_id = d.department_id;