Hints related to parallel execution are PARALLEL, NO_PARALLEL, USE_PX, and PQ_DISTRIBUTE.
PARALLEL Hint
The PARALLEL hint is a statement-level hint that instructs the optimizer to specify the number of parallel threads that can be used for a parallel operation. The hint overrides the value of PARALLEL_DEGREE_ POLICY. The hint can be used as a part of the SELECT, INSERT, UPDATE, and DELETE statements, or as a part of a table scan. The PARALLEL hint is ignored if any parallel limit is violated.
Syntax of the PARALLEL hint:
/*+ PARALLEL(n) */
The value of the n parameter in the PARALLE hint specifies the degree of parallelism (DOP). If the statement includes SORT BY or GROUP BY clauses, the number of available threads is twice the value specified by the PARALLEL hint.
In the following example, the specified DOP is 5:
SELECT /*+ PARALLEL(5) */ last_name
FROM emp;
OceanBase Database also supports table-level PARALLEL hints. Syntax:
/*+ PARALLEL(table_name n) */
If the global DOP and table-level DOP are both specified, the table-level DOP does not take effect.
NO_PARALLEL hint
The NO_PARALLEL hint instructs the optimizer to execute statements serially. It overrides the PARALLEL parameter for creating or changing the table.
Syntax of the NO_PARALLEL hint:
/*+ NO_PARALLEL ( [ @queryblock ] tablespec ) */
In the following example, the NO_PARALLEL hint overwrites the PARALLEL parameter value.
ALTER TABLE emp PARALLEL 6;
SELECT /*+ NO_PARALLEL(hr_emp) */ last_name
FROM emp hr_emp;
USE_PX hint
The USE_PX hint forces the server to execute SQL statements in parallel execution (PX) mode, which allows multithreading in statement execution. USE_PX and PARALLEL hints are often used in pairs.
Syntax of the USE_PX hint:
/*+ USE_PX */
Sample code of the USE_PX hint:
SELECT /*+ USE_PX PARALLEL(4)*/ e.dept_id, sum(e.salary)
FROM emp e
WHERE e.dept_id = 1001 GROUP BY e.dept_id;
PQ_DISTRIBUTE hint
The PQ_DISTRIBUTE hint instructs the optimizer on how to distribute rows between the producer server and the consumer server. The producer server is also known as the query server, and the consumer server is also known as the load server. You can use this hint to specify the row distribution method for the join or load. Syntax of the PQ_DISTRIBUTE hint:
/*+ PQ_DISTRIBUTE
( [ @queryblock ] tablespec
{ distribution | outer_distribution inner_distribution }
) */
Specify row distribution for joins
You can use two assignment methods to specify the row distribution for joins.
In the preceding syntax:
outer_distributionspecifies the data distribution mode for the left-side table.inner_distributionspecifies the data distribution mode for the right-side table.
The distribution methods are HASH, BROADCAST, PARTITION, and NONE. Only the six combinations of distribution methods in the following table are valid.
| Distribution method | Description |
|---|---|
| HASH, HASH | A hash function on the join key is used to map rows of each table to query servers. After the mapping is complete, each query server performs the join operation between a pair of resulting partitions. We recommend that you use this distribution method when the tables are comparable in size and the join operation is implemented by using HASH JOIN or SORT MERGE JOIN. |
| BROADCAST, NONE | All rows of the left-side table are broadcast to each query server. Rows of the right-side table are randomly partitioned. We recommend that you use this distribution method when the size of the left-side table is far smaller than that of the right-side table, or when the size of the right-side table multiplied by the number of query servers is greater than the size of the left-side table. |
| NONE, BROADCAST | All rows of the right-side table are broadcast to each query server. Rows of the left-side table are randomly partitioned. We recommend that you use this distribution method when the size of the right-side table is far smaller than that of the left-side table, or when the size of the right-side table multiplied by the number of query servers is smaller than that of the left-side table. |
| PARTITION, NONE | The rows of the left-side table are mapped by using the partitioning of the right-side table. The right-side table must be partitioned on the join keys. We recommend that you use this distribution method when the number of partitions of the left-side table equals or nearly equals a multiple of the number of query servers. For example, you can use this method when you have 14 partitions and 15 query servers. Notice If the right-side table is not partitioned or not equally joined on the partitioning key, the optimizer ignores this hint. |
| NONE, PARTITION | The rows of the right-side table are mapped by using the partitioning of the left-side table. The left-side table must be partitioned on the join keys. We recommend that you use this distribution method when the number of partitions of the left-side table equals or nearly equals a multiple of the number of query servers. For example, you can use this method when you have 14 partitions and 15 query servers. Notice If the left-side table is not partitioned or not equally joined on the partitioning key, the optimizer ignores this hint. |
| NONE, NONE | Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equally partitioned on the join keys. |
In the following example, tables r and s are joined by using a hash join, and the query contains a hint to use hash distribution:
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list
FROM r,s
WHERE r.c=s.c;
To broadcast the left-side table r, use the following query statement that includes a hint:
SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list
FROM r,s
WHERE r.c=s.c;