parallel hints syntax for PolarDB for MySQL - PolarDB - Alibaba Cloud Documentation Center

Version compatibility

Version	Supported hints
PolarDB for MySQL 8.0.1	`PARALLEL`, `NO_PARALLEL`
PolarDB for MySQL 8.0.2	`PARALLEL`, `NO_PARALLEL`, `PQ_DISTRIBUTE`, `PQ_GROUPBY`, `PQ_DISTINCT`, `PQ_WINDOW`, `PQ_ORDERBY`

Version 8.0.2 adds PQ_DISTRIBUTE for join distribution and PQ_GROUPBY, PQ_DISTINCT, PQ_WINDOW, and PQ_ORDERBY for aggregation and sorting execution modes.

Quick reference

Hint	Purpose	Version
`PARALLEL`	Enable parallel query; set DOP or target tables	8.0.1+
`NO_PARALLEL`	Disable parallel query globally or for specific tables	8.0.1+
`PQ_DISTRIBUTE`	Set distribution method for joins	8.0.2
`PQ_GROUPBY`	Set GROUP BY aggregation execution mode	8.0.2
`PQ_DISTINCT`	Set DISTINCT aggregation execution mode	8.0.2
`PQ_WINDOW`	Set window function execution mode	8.0.2
`PQ_ORDERBY`	Set ORDER BY sorting mode	8.0.2
`PQ_PUSHDOWN`	Run subqueries in parallel using the pushdown policy	-
`NO_PQ_PUSHDOWN`	Run subqueries in parallel using the shared access policy	-

Enable or disable parallel query

Enable

SELECT /*+PARALLEL(x)*/ ... FROM ...;   -- x > 0
SELECT /*+ SET_VAR(max_parallel_degree=n) */ * FROM ...   -- n > 0

Disable

SELECT /*+NO_PARALLEL()*/ ... FROM ...;
SELECT /*+ SET_VAR(max_parallel_degree=0) */ * FROM ...

PARALLEL and NO_PARALLEL

Specify tables for parallel scanning or exclude specific tables from parallel scans.

Syntax

/*+ PARALLEL [( [query_block] [table_name] [degree] )] */
/*+ NO_PARALLEL [( [query_block] [table_name][, table_name] )] */

Parameters

Parameter	Description
`query_block`	Name of the query block to which the hint applies.
`table_name`	Name of the table to which the hint applies.
`degree`	Degree of parallelism (DOP).

Note

The PARALLEL hint is ineffective for queries that do not support parallel processing or tables that do not support parallel scans.

PARALLEL() vs SET_VAR

Both set the DOP but differ in enforcement:

PARALLEL() forces parallel execution even when the table row count is below records_threshold_for_parallelism (default: 10,000).
SET_VAR(max_parallel_degree=n) sets the DOP but does not force parallel execution when the table row count is below records_threshold_for_parallelism.

Use PARALLEL() to guarantee parallel execution regardless of table size. Use SET_VAR to let the optimizer decide based on row count.

Examples

Force parallel query with default DOP

SELECT /*+PARALLEL()*/ * FROM t1, t2;

Forces parallel execution even when row count is below records_threshold_for_parallelism (default: 10000). DOP is determined by max_parallel_degree. If max_parallel_degree is 0, parallel query is disabled.

Force parallel query with a specific DOP

SELECT /*+PARALLEL(8)*/ * FROM t1, t2;

Forces parallel execution with DOP 8, even when row count is below records_threshold_for_parallelism.

Set DOP through SET_VAR

SELECT /*+ SET_VAR(max_parallel_degree=8) */ * FROM t1, t2;

Sets max_parallel_degree to 8. Unlike PARALLEL(), does not force parallel execution when row count is below records_threshold_for_parallelism.

Parallel scan on a specific table

SELECT /*+PARALLEL(t1)*/ * FROM t1, t2;

Forces parallel scan on t1 only.

SELECT /*+PARALLEL(t1 8)*/ * FROM t1, t2;

Forces parallel scan on t1 with a DOP of 8.

Parallel subqueries using query blocks

SELECT /*+PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1);

Forces parallel execution for the named query block subq1, using the default max_parallel_degree for DOP.

SELECT /*+PARALLEL(@subq1 8)*/ SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1);

Forces parallel execution for query block subq1 with a DOP of 8.

Parallel subqueries using inline hints

SELECT SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+PARALLEL()*/ SUM(t1.a) FROM t1);

Forces parallel execution for the subquery using the default max_parallel_degree.

SELECT SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+PARALLEL(8)*/ SUM(t1.a) FROM t1);

Forces parallel execution for the subquery with a DOP of 8.

Disable parallel query

SELECT /*+NO_PARALLEL()*/ * FROM t1, t2;

Disables parallel query for the entire statement.

Disable parallel query for specific tables

SELECT /*+NO_PARALLEL(t1)*/ * FROM t1, t2;

Disables parallel scans for t1 only. If parallel query is enabled, the system may still perform parallel scans on t2.

SELECT /*+NO_PARALLEL(t1, t2)*/ * FROM t1, t2;

Disables parallel scans for both t1 and t2.

Disable parallel subqueries

SELECT /*+NO_PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1);

SELECT SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+NO_PARALLEL()*/ SUM(t1.a) FROM t1);

Both statements disable parallel execution for the subquery.

PQ_DISTRIBUTE (join operations)

Control how data is distributed during parallel joins. Supports single-table distribution and two-table join distribution.

Syntax

Single-table distribution:

/*+ PQ_DISTRIBUTE([query_block] table_name strategy) */

Two-table join distribution — set the join mode between table_name and the previous table:

/*+ PQ_DISTRIBUTE([query_block] table_name strategy1 [strategy2]) */

With only strategy1, distribution applies to table_name. With both strategy1 and strategy2, they define the join distribution between table_name and the previous table.

Note

The previous table can be a physical table or an intermediate table from a prior join operation.

Parameters

Parameter	Description
`query_block`	Name of the query block to which the hint applies.
`table_name`	Name of the table to which the hint applies.
`strategy`	Distribution method. See valid values below.

Strategy values

Strategy	Description
`PQ_GATHER`	Aggregate data to a worker of the previous phase.
`PQ_HASH`	Shuffle and distribute data to multiple workers of the previous phase.
`PQ_BROADCAST`	Broadcast data to multiple workers of the previous phase.
`PQ_NONE`	Do not distribute data.

Examples

Single-table distribution

SELECT /*+ PARALLEL(t1) PQ_DISTRIBUTE(t1 PQ_GATHER) */ * FROM t AS t1;

Scans t1 in parallel. Results are gathered by the leader without redistribution.

SELECT /*+ PARALLEL(t1) PQ_DISTRIBUTE(t1 PQ_HASH) */ t1.a, SUM(t1.b) FROM t AS t1 GROUP BY t1.a;

Scans t1 in parallel and hash-distributes data to next-phase workers by group key.

Two-table join distribution

SELECT /*+ PARALLEL(t1) PARALLEL(t2) PQ_DISTRIBUTE(t2 PQ_HASH PQ_HASH) */ *
FROM t AS t1 STRAIGHT_JOIN t AS t2 ON t1.b = t2.c;

Both tables are scanned in parallel and hash-distributed to next-phase workers for a collocated join. The leader gathers final results.

SELECT /*+ PARALLEL(t1) PARALLEL(t2) PQ_DISTRIBUTE(t2 PQ_GATHER PQ_GATHER) */ *
FROM t AS t1 STRAIGHT_JOIN t AS t2 ON t1.b = t2.c;

Both tables are scanned in parallel and data is gathered by the leader, which completes the join.

Conflicting strategies

SELECT /*+ PARALLEL(t1) PARALLEL(t2) PQ_DISTRIBUTE(t2 PQ_HASH PQ_GATHER) */ *
FROM t AS t1 STRAIGHT_JOIN t AS t2 ON t1.b = t2.c;

The strategies conflict (PQ_HASH for t1 and PQ_GATHER for t2), so no parallel query plan can be generated.

PQ_GROUPBY (GROUP BY aggregation)

Control the parallel execution mode for GROUP BY aggregation.

Syntax

/*+ PQ_GROUPBY(strategy) */

Strategy values

Strategy	Description
`PQ_ONEPHASE`	Distribute data to workers by group key. Workers perform aggregation in parallel.
`PQ_TWOPHASE_GATHER`	Each worker aggregates separately. The leader merges partial results into the final result.
`PQ_TWOPHASE_HASH`	Each worker aggregates separately, then distributes data to the next-phase workers by group key for final aggregation.
`PQ_SERIAL`	Perform GROUP BY aggregation serially.

Examples

SELECT /*+ PARALLEL(t1) PQ_GROUPBY(PQ_ONEPHASE) */ t1.a, SUM(t1.b) FROM t AS t1 GROUP BY t1.a;

Scans t1 in parallel and distributes data to next-phase workers by t1.a. Workers aggregate in parallel; the leader gathers results.

SELECT /*+ PARALLEL(t1) PQ_GROUPBY(PQ_TWOPHASE_HASH) */ t1.a, SUM(t1.b) FROM t AS t1 GROUP BY t1.a;

Workers scan t1 in parallel and aggregate locally. Intermediate results are redistributed by t1.a for final aggregation. The leader gathers results.

PQ_DISTINCT (DISTINCT aggregation)

Control the parallel execution mode for DISTINCT operations.

Syntax

/*+ PQ_DISTINCT(strategy) */

Strategy values

Strategy	Description
`PQ_TWOPHASE_GATHER`	Each worker aggregates separately. The leader merges partial results into the final result.
`PQ_SERIAL`	Perform DISTINCT aggregation serially.

PQ_WINDOW (window functions)

Control the parallel execution mode for window functions.

Syntax

/*+ PQ_WINDOW([window_name] strategy) */

Note

A hint that specifies a window_name takes priority over a hint without one.

Parameters

Parameter	Description
`window_name`	Window function to apply the strategy to. If omitted, the strategy applies to all window functions.
`strategy`	Execution mode. See valid values below.

Strategy values

Strategy	Description
`PQ_ONEPHASE`	Distribute data by the PARTITION BY clause. Workers compute the window function in parallel.
`PQ_SERIAL`	Perform window function computation serially.

Example

SELECT /*+ PQ_WINDOW(PQ_ONEPHASE) PQ_WINDOW(win PQ_SERIAL) */
  ROW_NUMBER() OVER(win) AS 'row_number',
  RANK() OVER(PARTITION BY name ORDER BY salary DESC)
FROM employee_salaries WINDOW win AS (PARTITION BY dept ORDER BY salary DESC);

The named window win runs serially. All other window functions are distributed to workers by the PARTITION BY key for parallel computation.

PQ_ORDERBY (sorting)

Control the parallel execution mode for ORDER BY sorting.

Syntax

/*+ PQ_ORDERBY(strategy) */

Strategy values

Strategy	Description
`PQ_TWOPHASE_GATHER`	Workers sort data in parallel. The leader merges partial results into the final sorted result.
`PQ_SERIAL`	Perform sorting serially.

PQ_PUSHDOWN and NO_PQ_PUSHDOWN (parallel subqueries)

Control the parallel execution policy for subqueries. Subquery support details are documented in View parallel query execution plans.

Syntax

/*+ PQ_PUSHDOWN [( [query_block])] */       -- Pushdown policy
/*+ NO_PQ_PUSHDOWN [( [query_block])] */    -- Shared access policy

Examples

Pushdown policy:

EXPLAIN SELECT /*+ PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ QB_NAME(qb1) */ a FROM t1);

Shared access policy:

EXPLAIN SELECT /*+ NO_PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ QB_NAME(qb1) */ a FROM t1);

Inline hint without a query block reference:

EXPLAIN SELECT * FROM t2 WHERE t2.a =
                 (SELECT /*+ NO_PQ_PUSHDOWN() */ a FROM t1);