Parallel hints allow you to specify whether to enable parallel queries. You can also specify the degree of parallelism (DOP) and the tables on which parallel scans are performed. PolarDB supports the following hints in parallel queries: PARALLEL and NO_PARALLEL.

Enable or disable parallel queries

  • Enable parallel queries
    You can execute one of the following statements to enable the parallel query feature:
    • SELECT /*+PARALLEL(x)*/ ... FROM ...;   -- x >0
    • SELECT /*+ SET_VAR(max_parallel_degree=n) */  *  FROM ...   // n > 0
  • Disable parallel queries
    You can execute one of the following statements to disable the parallel query feature:
    • SELECT /*+NO_PARALLEL()*/ ... FROM ...;
    • SELECT /*+ SET_VAR(max_parallel_degree=0) */  *  FROM ...

Advanced hint usage

The parallel query feature supports two hints: PARALLEL and NO_PARALLEL.
  • You can use the PARALLEL hint to enforce parallel queries and specify the DOP and the table on which parallel scans are performed. The following syntax is supported:
    /*+ PARALLEL [( [query_block] [table_name]  [degree] )] */
  • You can use the NO_PARALLEL hint to enforce sequential queries or specify the tables for which parallel scans are prohibited.
    /*+ NO_PARALLEL [( [query_block] [table_name][, table_name] )] */

The following table describes the parameters.

Parameter Description
query_block The name of the query block to which the hint is applied.
table_name The name of the table to which the hint is applied.
degree The DOP.

Examples:

SELECT /*+PARALLEL()*/ * FROM t1, t2; 
-- When the number of rows in the table is less than the value of records_threshold_for_parallelism, parallel queries are forcibly executed. The default value of records_threshold_for_parallelism is 10000.
-- The default max_parallel_degree parameter is used to specify the DOP. If max_parallel_degree is set to a value greater than 0, 
-- parallel queries are enabled. If max_parallel_degree is set to 0, parallel queries are disabled.

SELECT /*+PARALLEL(8)*/ * FROM t1, t2; 
-- This statement enforces parallel queries in a DOP of 8. 
-- When the number of rows in the table is less than the value of records_threshold_for_parallelism, parallel queries are forcibly executed. The default value of records_threshold_for_parallelism is 10000.
-- The max_parallel_degree parameter is set to 8.

SELECT /*+ SET_VAR(max_parallel_degree=8) */  *  FROM ...   
-- The max_parallel_degree parameter is set to 8. 
-- When the number of rows in the table is less than the value of records_threshold_for_parallelism, such as 20000, parallel queries are automatically disabled.

SELECT /*+PARALLEL(t1)*/ * FROM t1, t2; 
-- This statement enforces parallel queries on table t1 by using the /*+PARALLEL()*/ syntax for table t1.

SELECT /*+PARALLEL(t1 8)*/ * FROM t1, t2; 
-- This statement enforces parallel queries on table t1 in a DOP of 8 by using the /*+PARALLEL(8)*/ syntax for table t1.

SELECT /*+PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1); 
-- This statement enforces parallel subqueries. The default parameter max_parallel_degree is used to specify the DOP. 
-- If max_parallel_degree is set to a value greater than 0, parallel queries are enabled. If max_parallel_degree is set to 0, parallel queries are disabled.

SELECT /*+PARALLEL(@subq1 8)*/ SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1); 
-- This statement enforces parallel subqueries. The max_parallel_degree parameter is set to 8.

SELECT SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+PARALLEL()*/ SUM(t1.a) FROM t1); 
-- This statement enforces parallel subqueries. 
-- The default max_parallel_degree parameter is used to specify the DOP. 
-- If max_parallel_degree is set to a value greater than 0, parallel queries are enabled. If max_parallel_degree is set to 0, parallel queries are disabled.

SELECT SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+PARALLEL(8)*/ SUM(t1.a) FROM t1); 
-- This statement enforces parallel subqueries. The max_parallel_degree parameter is set to 8.

SELECT /*+NO_PARALLEL()*/ * FROM t1, t2; 
-- This statement disables parallel queries.

SELECT /*+NO_PARALLEL(t1)*/ * FROM t1, t2; 
-- This statement prohibits parallel queries for only table t1. If the system enables parallel queries, the system may perform parallel scans and run parallel queries on table t2.

SELECT /*+NO_PARALLEL(t1, t2)*/ * FROM t1, t2; 
-- This statement prohibits parallel queries for table t1 and table t2.

SELECT /*+NO_PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1); 
-- This statement prohibits parallel subqueries.

SELECT SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+NO_PARALLEL()*/ SUM(t1.a) FROM t1); 
 -- This statement prohibits parallel subqueries.
Note The PARALLEL hint is ineffective for queries that do not support parallel processing or tables that do not support parallel scans.

Parallel subqueries

You can also use hints to specify the policy to run parallel subqueries. The following hint syntax is provided:

/*+ PQ_PUSHDOWN [( [query_block])] */ The subqueries run in parallel based on the pushdown policy.
/*+ NO_PQ_PUSHDOWN [( [query_block])] */ The subqueries run in parallel based on the shared access policy.

Examples:

# The subqueries run in parallel based on the pushdown policy.
EXPLAIN SELECT /*+ PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ qb_name(qb1) */ a FROM t1);

# The subqueries run in parallel based on the shared access policy.
EXPLAIN SELECT /*+ NO_PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ qb_name(qb1) */ a FROM t1);
# No hint is added to the query block.
EXPLAIN SELECT * FROM t2 WHERE t2.a =
                 (SELECT /*+ NO_PQ_PUSHDOWN() */ a FROM t1);