In SQL queries, the following are the hints related to join operations, including those that enable or disable specific join algorithms:
Hint type |
Description |
|---|---|
USE_MERGE |
When the table specified in this hint serves as the right table of the join, the sorted merge join algorithm is used. Its reverse operation is NO_USE_MERGE. |
NO_USE_MERGE |
When the table specified in this hint is used as the right table for the join, the sorted merge join algorithm is not used. Its reverse operation is USE_MERGE. |
USE_HASH |
When the table specified in this hint serves as the right table of the join, the HASH-JOIN algorithm is used. Its reverse operation is NO_USE_HASH. |
NO_USE_HASH |
When the table specified in this hint is used as the right table of the join, the HASH-JOIN algorithm is not used. Its reverse operation is USE_HASH. |
USE_NL |
When the table specified in this hint serves as the left table of the join, the NL-JOIN algorithm is used. Its reverse operation is NO_USE_NL. |
NO_USE_NL |
When the table specified in this hint is used as the left table of the join, the NL-JOIN algorithm is not used. Its reverse operation is USE_NL. |
PQ_DISTRIBUTE |
Controls the data allocation method for join operations. |
PQ_MAP |
Specifies the follower mapping strategy for connection operations. |
USE_NL_MATERIALIZATION |
Forces materialization of the left table in nested-loop joins. Its reverse operation is NO_USE_NL_MATERIALIZATION. |
NO_USE_NL_MATERIALIZATION |
Prevents the left table from being nested in a materialized nested-loop join. Its reverse operation is USE_NL_MATERIALIZATION. |
PX_JOIN_FILTER |
Instructs the optimizer to control the use of JOIN FILTER in HASH JOIN. Its reverse operation is NO_PX_JOIN_FILTER. |
NO_PX_JOIN_FILTER |
Instructs the optimizer to disable JOIN FILTER for HASH JOIN. Its reverse operation is PX_JOIN_FILTER. |
PX_PART_JOIN_FILTER |
It is used to instruct the optimizer to manually enable PART FILTER. Its reverse operation is NO_PX_PART_JOIN_FILTER. |
NO_PX_PART_JOIN_FILTER |
It is used to instruct the optimizer to manually disable PART FILTER. Its reverse operation is NO_PX_PART_JOIN_FILTER. |
USE_MERGE Hint
When the table specified in this hint is used as the right table for the join, the sorting merge join algorithm is used. Its reverse operation is NO_USE_MERGE.
Syntax
/*+ USE_MERGE ( [ @queryblock ] tablespec [ tablespec ]... ) */
Considerations
It is recommended to use the
USE_NLandUSE_MERGEhints together with theLEADINGorORDEREDhint.The optimizer will use these hints when the referenced table is the right table of the join.
If the referenced table is the left table, the hint is ignored.
USE_MERGEspecifies to use theMERGE JOINalgorithm when the table is the right table.In OceanBase AI Database, the
MERGE JOINalgorithm requires an equality condition (join-condition). Therefore, when joining two tables without an equality condition,USE_MERGEbecomes invalid.
Examples
-- Use the USE_MERGE hint to instruct the optimizer to use the sort-merge join algorithm to execute queries.
-- For the join operation between the employees and departments tables, the employees table serves as the right table, and the departments table serves as the left table.
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 that use the USE_MERGE hint when the specified table is used as the left table for a join with another row resource. Its reverse operation is USE_MERGE.
Syntax
/*+ NO_USE_MERGE ( [ @queryblock ] tablespec [ tablespec ]... ) */
Examples
-- Use the NO_USE_MERGE hint to instruct the optimizer not to use the sorted merge join algorithm to execute queries.
-- For the join operation between the employees and departments tables, the sort-merge join algorithm will be excluded.
SELECT /*+ NO_USE_MERGE(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;
USE_HASH Hint
When the table specified by the USE_HASH hint is used as the right table for the join, the HASH-JOIN algorithm is used. Its reverse operation is NO_USE_HASH.
Syntax
/*+ USE_HASH ( [ @queryblock ] tablespec [ tablespec ]... ) */
Examples
-- Use the USE_HASH hint to instruct the optimizer to use the hash join algorithm (HASH-JOIN) to execute queries.
-- For the join operation between the orders and order_items tables, the orders table serves as the right table, and the order_items table serves as the left table.
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 specifies that a table, when used as the right table in a join, will not use the HASH-JOIN algorithm. Its counterpart is the USE_HASH hint.
Syntax
/*+ NO_USE_HASH ( [ @queryblock ] tablespec [ tablespec ]... ) */
Examples
-- Use the NO_USE_HASH hint to instruct the optimizer not to use the hash join algorithm to execute the query.
-- The HASH-JOIN algorithm will be excluded for the join operation between the employees and departments tables.
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 specifies that the table, when used as the left table in a join, should be joined using the nested-loop join (NL-JOIN) algorithm. Its reverse operation is NO_USE_NL.
We recommend that you use the
USE_NLandUSE_MERGhints together with theLEADINGorORDEREDhint.The optimizer uses these hints when the referenced table is the left table in a join.
If the referenced table is a right table, the hint is ignored.
Syntax
/*+ USE_NL ( [ @queryblock ] tablespec [ tablespec ]... ) */
Examples
As shown in the query example below, the hint forces the execution of nested loops. It accesses the orders table through a full-table scan and applies the filter condition l.order_id = h.order_id to each row. For each row that meets the filter condition, it accesses the order_items table via the order_id index.
-- Use the USE_NL hint to instruct the optimizer to use the nested-loop join algorithm (NL-JOIN) to execute queries.
-- For the join operation between the orders and order_items tables, the orders table serves as the right table, and the order_items table serves as the left table.
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 specifies that when a table is used as the left table in a join, the nested-loop join (NL-JOIN) algorithm will not be used. Its counterpart is the USE_NL hint.
Syntax
/*+ NO_USE_NL ( [ @queryblock ] tablespec [ tablespec ]... ) */
Examples
-- Use the NO_USE_NL hint to instruct the optimizer not to use the nested-loop join algorithm to execute queries.
-- For the join operation between the employees and departments tables, the NL-JOIN algorithm will be excluded.
SELECT /*+ NO_USE_NL(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;
PQ_DISTRIBUTE Hint
The PQ_DISTRIBUTE hint is used to instruct the optimizer how to distribute data between the producer (the server that generates the row data for the query results) and consumer (the server that receives and processes this row data) servers in a parallel query. You can use the PQ_DISTRIBUTE hint to control the distribution of row data during join operations or load operations.
In parallel query scenarios, especially when processing large volumes of data, PQ_DISTRIBUTE can optimize resource usage and improve query performance.
Syntax
/*+ PQ_DISTRIBUTE
( [ @queryblock ] tablespec
{ distribution | outer_distribution inner_distribution }
) */
Control the allocation of connections
You can control the distribution method of joins by specifying two allocation methods.
As shown in the syntax below:
outer_distributionspecifies the data distribution method for the left table.inner_distributionspecifies the data distribution method for the right table.
Distribution methods include HASH, BROADCAST, PARTITION, and NONE. Only the following six combinations of distribution methods are valid:
Distribution Method |
Description |
|---|---|
| HASH, HASH | The hash function on the join key is used to map rows from each table to the query servers. After mapping, each query server performs the join between a pair of result partitions. This distribution method is recommended when the tables are of comparable size and the join operation is implemented using a hash join or a sort-merge join. |
| BROADCAST, NONE | All rows of the right table are broadcast to each query server. The rows of the left table are randomly partitioned. This distribution method is recommended when the right table is significantly smaller than the left table. It is also generally recommended when the size of the left table multiplied by the number of query servers is greater than the size of the right table. |
| NONE, BROADCAST | All rows from the left table are broadcast to each query server. The rows in the right table are randomly partitioned. This distribution method is recommended when the left table is significantly smaller than the right table. It is also generally recommended when the size of the left table multiplied by the number of query servers is less than the size of the right table. |
| PARTITION, NONE | Rows in the left table are mapped to partitions of the right table. The left table must be partitioned on the join key. This distribution method is recommended when the number of partitions in the right table is equal to or nearly a multiple of the number of query servers. For example, with 14 partitions and 15 query servers. Note If the left table is not partitioned or not evenly partitioned on the join key, the optimizer will ignore this hint. |
| NONE, PARTITION | Rows in the right table are mapped to partitions of the left table. The right table must be partitioned on the join key. This distribution method is recommended when the number of partitions in the right table is equal to or nearly a multiple of the number of query servers. For example, with 14 partitions and 15 query servers. Note If the right table is not partitioned on the join key or if the join is not balanced, the optimizer will ignore this hint. |
| NONE, NONE | Each query server performs a join operation between a pair of matching partitions, with one partition from each table. The two tables must be evenly divided on the join key. |
Examples
As shown in the query example below, a hash join is specified to join the two tables r and s. The following query contains a hint that uses the hash distribution method:
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s) */ column_list
FROM r, s
WHERE r.c = s.c;
To broadcast the right table r, the query statement containing the hint is as follows:
SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list
FROM r, s
WHERE r.c = s.c;
USE_NL_MATERIALIZATION Hint
The USE_NL_MATERIALIZATION hint forcibly instructs the optimizer to generate a materialization operator to cache data when the specified table is designated as the left table (subtree). Its counterpart is the NO_USE_NL_MATERIALIZATION hint.
Syntax
/*+ USE_NL_MATERIALIZATION ( [ @queryblock ] tablespec [ tablespec ]... ) */
Examples
-- Use the USE_NL_MATERIALIZATION hint to instruct the optimizer to materialize the departments table in nested-loop joins.
SELECT /*+ USE_NL_MATERIALIZATION(departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;
NO_USE_NL_MATERIALIZATION Hint
The NO_USE_NL_MATERIALIZATION hint forcibly instructs the optimizer to avoid generating a materialization operator to cache data when the specified table is designated as the left table (subtree). Its counterpart is the USE_NL_MATERIALIZATION hint.
Syntax
/*+ NO_USE_NL_MATERIALIZATION ( [ @queryblock ] tablespec [ tablespec ]... ) */
Examples
-- Use the NO_USE_NL_MATERIALIZATION hint to prevent the optimizer from materializing the departments table in nested-loop joins.
-- This means that the data in the departments table is re-accessed with each loop join, rather than using the cached materialized results.
SELECT /*+ NO_USE_NL_MATERIALIZATION(departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;
Join Filter Hint
There are four Join Filter-related hints in total. The first two are used to control general Join Filters, while the last two are used to control partial Join Filters:
PX_JOIN_FILTERHintNO_PX_JOIN_FILTERHintPX_PART_JOIN_FILTERHintNO_PX_PART_JOIN_FILTERHint
Note that these four hints take effect in a parallel execution environment and have no significant effect in a non-parallel environment.
Their syntax and parameter descriptions are as follows:
PX_JOIN_FILTER Hint
In a parallel execution environment, the PX_JOIN_FILTER Hint instructs the optimizer to enable JOIN FILTER for HASH JOIN. Using this hint allows you to specify a particular table as the right table for a hash join and use a join filter for runtime filtering. Its reverse operation is NO_PX_JOIN_FILTER.
Syntax
/*+ PX_JOIN_FILTER ( [ @qb_name ] filter_table [ left_tables ] [real_filter_table]) */
Parameter description
qb_name: Optional. Specifies the query block for which the hint takes effect.filter_table: Describes the single table from which the Join Filter is pushed down. If it is a subquery, this should be the name of the view.left_tables: Optional. Specifies the left table for the HASH-JOIN when allocating the Join Filter.real_filter_table: The single table in the subquery from which the Join Filter is actually pushed down.
NO_PX_JOIN_FILTER Hint
The NO_PX_JOIN_FILTER Hint is used to instruct the optimizer to disable JOIN FILTER for HASH JOIN. Its reverse operation is PX_JOIN_FILTER.
Syntax
/*+ NO_PX_JOIN_FILTER( table ) */
PX_PART_JOIN_FILTER Hint
The PX_PART_JOIN_FILTER Hint is used to instruct the optimizer to manually enable PART FILTER. Its reverse operation is NO_PX_PART_JOIN_FILTER.
Syntax
/*+ PX_PART_JOIN_FILTER ( [ @qb_name ] filter_table [ left_tables ] [real_filter_table]) */
NO_PX_PART_JOIN_FILTER Hint
The NO_PX_PART_JOIN_FILTER Hint is used to instruct the optimizer to manually disable PART FILTER. Its reverse operation is PX_PART_JOIN_FILTER.
Syntax
/*+ NO_PX_PART_JOIN_FILTER (table) */
Application scenarios
The four Join Filter hints (PX_JOIN_FILTER, NO_PX_JOIN_FILTER, PX_PART_JOIN_FILTER, and NO_PX_PART_JOIN_FILTER) are typically used together with the leading and use_hash hints. If not used in conjunction with leading and use_hash, these hints may become invalid due to other join order and join algorithm generation.
General scenarios
The Join Filter hint is generally used together with LEADING and USE_HASH. Otherwise, it may become invalid due to different join order or join algorithm generation.
First, create a partitioned table:
CREATE TABLE t1 (
c1 INT,
c2 INT,
c3 INT,
c4 INT
) PARTITION BY HASH(c1) PARTITIONS 10;
Forced use of join filters
You can use the following SQL statement to enforce join filters:
EXPLAIN SELECT
/*+ PARALLEL(2) LEADING(a b) USE_HASH(b) PQ_DISTRIBUTE(b BC2HOST NONE)
PX_JOIN_FILTER(b)
PX_PART_JOIN_FILTER(b)
*/ *
FROM t1 a, t1 b WHERE a.c1 = b.c1;
or:
EXPLAIN SELECT
/*+ PARALLEL(2) LEADING(a b) USE_HASH(b) PQ_DISTRIBUTE(b BC2HOST NONE)
PX_JOIN_FILTER(b a)
PX_PART_JOIN_FILTER(b a)
*/ *
FROM t1 a, t1 b WHERE a.c1 = b.c1;
The following example shows the output execution plan:
===============================================================
| ID | OPERATOR | NAME | EST. ROWS | COST |
---------------------------------------------------------------
| 0 | PX COORDINATOR | | 1 | 456 |
| 1 | EXCHANGE OUT DISTR | :EX10001| 1 | 456 |
| 2 | SHARED HASH JOIN | | 1 | 455 |
| 3 | JOIN FILTER CREATE | :BF0001 | 1 | 228 |
| 4 | PART JOIN FILTER CREATE | :BF0000 | 1 | 228 |
| 5 | EXCHANGE IN DISTR | | 1 | 228 |
| 6 | EXCHANGE OUT DISTR (BC2HOST)| :EX10000| 1 | 228 |
| 7 | PX BLOCK ITERATOR | | 1 | 228 |
| 8 | TABLE SCAN | a | 1 | 228 |
| 9 | JOIN FILTER USE | :BF0001 | 1 | 228 |
| 10 | PX BLOCK HASH JOIN-FILTER | :BF0000 | 1 | 228 |
| 11 | TABLE SCAN | b | 1 | 228 |
===============================================================
Multi-table scenarios
For a three-table join, when the left table is specified as a, you can generate a join filter for the right table c:
EXPLAIN SELECT
/*+ PARALLEL(2) LEADING(a (b c)) USE_HASH(c (b c)) PQ_DISTRIBUTE((b c) BC2HOST NONE) PQ_DISTRIBUTE(c BC2HOST NONE)
NO_PX_JOIN_FILTER(c)
NO_PX_JOIN_FILTER(b)
NO_PX_PART_JOIN_FILTER(c)
NO_PX_PART_JOIN_FILTER(b)
PX_JOIN_FILTER(c a)
*/ *
FROM t1 a, t1 b, t1 c WHERE a.c1 = c.c1 AND b.c1 = c.c1;
The following is an example of the output execution plan:
===============================================================
| ID | OPERATOR | NAME | EST. ROWS | COST |
---------------------------------------------------------------
| 0 | PX COORDINATOR | | 1 | 684 |
| 1 | EXCHANGE OUT DISTR | :EX10002| 1 | 683 |
| 2 | SHARED HASH JOIN | | 1 | 683 |
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 228 |
| 4 | EXCHANGE IN DISTR | | 1 | 228 |
| 5 | EXCHANGE OUT DISTR (BC2HOST) | :EX10000| 1 | 228 |
| 6 | PX BLOCK ITERATOR | | 1 | 228 |
| 7 | TABLE SCAN | a | 1 | 228 |
| 8 | SHARED HASH JOIN | | 1 | 455 |
| 9 | EXCHANGE IN DISTR | | 1 | 228 |
| 10 | EXCHANGE OUT DISTR (BC2HOST) | :EX10001| 1 | 228 |
| 11 | PX BLOCK ITERATOR | | 1 | 228 |
| 12 | TABLE SCAN | b | 1 | 228 |
| 13 | JOIN FILTER USE | :BF0000 | 1 | 228 |
| 14 | PX BLOCK ITERATOR | | 1 | 228 |
| 15 | TABLE SCAN | c | 1 | 228 |
===============================================================
Similarly, for a three-table join where table b is specified as the left table, you can generate a Join Filter for the right table c:
EXPLAIN SELECT
/*+ PARALLEL(2) LEADING(a (b c)) USE_HASH(c (b c)) PQ_DISTRIBUTE((b c) BC2HOST NONE) PQ_DISTRIBUTE(c BC2HOST NONE)
NO_PX_JOIN_FILTER(c)
NO_PX_JOIN_FILTER(b)
NO_PX_PART_JOIN_FILTER(c)
NO_PX_PART_JOIN_FILTER(b)
PX_JOIN_FILTER(c b)
*/ *
FROM t1 a, t1 b, t1 c WHERE a.c1 = c.c1 AND b.c1 = c.c1;
The following is an example of the output execution plan:
===============================================================
| ID | OPERATOR | NAME | EST. ROWS | COST |
---------------------------------------------------------------
| 0 | PX COORDINATOR | | 1 | 684 |
| 1 | EXCHANGE OUT DISTR | :EX10002| 1 | 683 |
| 2 | SHARED HASH JOIN | | 1 | 683 |
| 3 | EXCHANGE IN DISTR | | 1 | 228 |
| 4 | EXCHANGE OUT DISTR (BC2HOST) | :EX10000| 1 | 228 |
| 5 | PX BLOCK ITERATOR | | 1 | 228 |
| 6 | TABLE SCAN | a | 1 | 228 |
| 7 | SHARED HASH JOIN | | 1 | 455 |
| 8 | JOIN FILTER CREATE | :BF0000 | 1 | 228 |
| 9 | EXCHANGE IN DISTR | | 1 | 228 |
| 10 | EXCHANGE OUT DISTR (BC2HOST) | :EX10001| 1 | 228 |
| 11 | PX BLOCK ITERATOR | | 1 | 228 |
| 12 | TABLE SCAN | b | 1 | 228 |
| 13 | JOIN FILTER USE | :BF0000 | 1 | 228 |
| 14 | PX BLOCK ITERATOR | | 1 | 228 |
| 15 | TABLE SCAN | c | 1 | 228 |
=======================================================================
Conflict resolution for hints
PX_JOIN_FILTER and NO_PX_JOIN_FILTER can take four valid forms depending on whether the left tables left_tables are specified. The matching and usage follow the priority order shown in the following table:
Hint |
Feature |
|---|---|
| NO_PX_JOIN_FILTER( a (b c) ) | When the left table is (b c), using join filter on a in the right table is prohibited. |
| PX_JOIN_FILTER( a (b c) ) | When the left table is (b c), use join filter on a in the right table. |
| NO_PX_JOIN_FILTER( a ) | Join any table on the left and prohibit joins on the right table based on the following conditions: a Use join filter |
| PX_JOIN_FILTER( a ) | Join the left table to any row in the right table. a Use join filter |
The conflict handling for PX_PART_JOIN_FILTER and NO_PX_PART_JOIN_FILTER is the same as that for PX_JOIN_FILTER.