Hints related to joins

Hints related to joins are USE_MERGE, NO_USE_MERGE, USE_HASH, NO_USE_HASH, USE_NL, NO_USE_NL, USE_BNL, and NO_USE_BNL.

USE_MERGE hint

The USE_MERGE hint instructs the optimizer to use a sort-merge join to join each specified table with another row source and use the specified table as the right-side table.

Syntax of the USE_MERGE hint:

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

We recommend that you use the USE_NL and USE_MERG hints with the LEADING or ORDERED hint. If the referenced tables are right-side tables, the optimizer uses these hints. If the referenced tables are left-side tables, the optimizer ignores these hints. The USE_MERGE hint specifies to use the MERGE-JOIN algorithm when the specified table is a right-side table. In OceanBase Database, to use the MERGE-JOIN algorithm, the tables to be joined must have equivalent join conditions. When you join two tables that do not have equivalent conditions, the USE_MERGE hint is invalid.

Sample code 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 joins used by the USE_MERGE hint when the optimizer uses the specified table as a right-side table and joins the table with another row source.

Syntax of the NO_USE_MERGE hint:

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

Sample code 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 use the HASH-JOIN algorithm to join each specified table with another row source and use the specified table as the right-side table.

Syntax of the USE_HASH hint:

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

Sample code 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 used by the USE_HASH hint when the optimizer uses the specified table as a right-side table and joins the table with another row source.

Syntax of the NO_USE_HASH hint:

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

Sample code 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 use a nest-loop (NL) join to join each specified table to another row source and use the specified table as a right-side table with the NL join algorithm.

Syntax of the USE_NL hint:

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

We recommend that you use the USE_NL and USE_MERG hints with the LEADING or ORDERED hint. If the referenced tables are right-side tables, the optimizer uses these hints. If the referenced tables are left-side tables, the optimizer ignores these hints.

In the following example, a nested loop is forced by using a hint, the orders table is accessed by using a full table scan, and the filter condition l.order_id = h.order_id is applied to each row. For each row that meets the filter condition, order_items is accessed by using 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 the nested-loop joins when the optimizer uses the specified table as a right-side table and joins the table with another row source.

Syntax of the NO_USE_NL hint:

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

Sample code 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 use a block-nested loop (BNL) join to join each specified table to another row source and use the specified table as a right-side table with the BNL join algorithm.

Syntax of the USE_BNL hint:

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

We recommend that you use the USE_BN hint with the LEADING or ORDERED hint. If the referenced tables are right-side tables, the optimizer uses these hints. If the referenced tables are left-side tables, the optimizer ignores these hints.

In the following example, the hint forcibly executes the block nested loops, accesses the orders table by table scanning, and applies the filtering conditions l.order_id = h.order_id to each row. For each row that meets the filter condition, order_items is accessed by using 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 used by the USE_BNL hint when the optimizer uses the specified table as a right-side table and joins the table to another row resource.

Syntax of the NO_USE_BNL hint:

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

Sample code of the NO_USE_BNL hint:

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