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.

The syntax of the USE_MERGE hint is as follows:

/*+ 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.

Here is 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 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.

The syntax of the NO_USE_MERGE hint is as follows:

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

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

The syntax of the USE_HASH hint is as follows:

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

Here is 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 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.

The syntax of the NO_USE_HASH hint is as follows:

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

Here is 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 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.

The syntax of the USE_NL hint is as follows:

/*+ 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.

The syntax of the NO_USE_NL hint is as follows:

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

Here is 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 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.

The syntax of the USE_BNL hint is as follows:

/*+ 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.

The syntax of the NO_USE_BNL hint is as follows:

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

Here is 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;