The optimizer of OceanBase Database can optimize SQL queries through parallel execution. In the optimizer, the degree of parallelism (DOP) indicates the amount of parallel resources. In actual business scenarios, you need to determine the DOP based on your query and business requirements.
OceanBase Database supports enabling parallel execution for queries through auto DOP, hints, system variables, and schema DOP. However, parallel execution is not supported in the following scenarios:
- Parallel execution is not supported for queries involving PL/UDF or dblink.
- For base tables scanned through DAS, the DOP is fixed at 1, which cannot be changed.
If parallel execution is supported for a query, the DOP of the query can be set through the following methods in descending order of priority:
- DOP configured in a table-level parallel hint
- DOP configured in a global parallel hint
- DOP configured in system variables
- Schema DOP
Configure DOP by using a table-level hint
You can use a table-level hint to specify the DOP of a table object. The basic syntax is as follows:
/*+PARALLEL ( @qb_name table_name dop_value)*/
In this example, query Q1 specifies to execute the t1 table in parallel with a DOP of 8 and the t2 table in parallel with a DOP of 4. Here is an example:
Q1: SELECT /*+PARALLEL(t1 8) PARALLEL(t2 4)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
For tables that are not specified with hints, the system determines the DOP based on lower-priority methods. In query Q2, the t1 table is specified with a DOP of 8, and the t2 table is scanned without parallel execution being enabled, namely, with a DOP of 1.
Q2: SELECT /*+PARALLEL(t1 8)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Configure DOP by using a global hint
You can use a global hint to specify the default DOP for a query. The basic syntax is as follows:
/*+PARALLEL ( dop_value )*/
/*+PARALLEL ( AUTO )*/
/*+PARALLEL ( MANUAL )*/
In this example, the PARALLEL( 8 ) hint in query Q3 specifies to use a DOP of 8 for both the t1 and t2 tables.
You can also use a global hint to specify the DOP acquisition strategy for a query. For example, the PARALLEL ( AUTO ) hint in query Q4 specifies to use the auto DOP strategy for all tables in the query.
The global hint has a lower priority than a table-level hint. When both global and table-level hints are present, the global hint takes effect only for tables that are not specified with table-level hints. In query Q5, the t1 table is specified with a DOP of 8 based on the table-level hint, and the t2 table is specified with a DOP of 4 based on the global hint. In query Q6, the t1 table is specified with a DOP of 8 based on the table-level hint, and the t2 table is specified with a DOP determined based on the auto DOP strategy.
Q3: SELECT /*+PARALLEL(8)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Q4: SELECT /*+PARALLEL(AUTO)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Q5: SELECT /*+PARALLEL(t1 8) PARALLEL(4)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Q6: SELECT /*+PARALLEL(t1 8) PARALLEL ( AUTO )*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Configure DOP by using a system variable
There are two types of system variables related to DOP: one for configuring the DOP acquisition strategy, and the other for configuring a fixed DOP.
You can set the parallel_degree_policy variable at the GLOBAl or SESSION level to enable the auto DOP strategy. In the auto DOP strategy, the system calculates the DOP of a table object based on a specific algorithm. For more information, see Enable the auto DOP strategy.
If you specify a DOP by using a global PARALLEL hint, enable the auto DOP strategy, or enable the auto DOP strategy by setting the parallel_degree_policy variable, the fixed DOP becomes invalid. In other cases, you can set the following internal system variables to configure the DOP for a query or DML operation. Here is an example:
SET _enable_parallel_query = 1;
SET _enable_parallel_dml = 1;
SET _force_parallel_query_dop = 8;
SET _force_parallel_dml_dop = 8;
Schema DOP
When you use the fixed DOP strategy, if you enable parallel execution by using an internal system variable, but set the DOP to 1, the query uses the DOP specified when the table was created to enable parallel execution for the table object.
SET _enable_parallel_query = 1;
SET _enable_parallel_dml = 1;
SET _force_parallel_query_dop = 1;
SET _force_parallel_dml_dop = 1;
In the following example, the DOP specified when creating the t1 table is 8. If you use the ALTER TABLE statement to change the DOP of the t1 table to 4 and use the ALTER INDEX statement to change the DOP of the idx1 index to 2, parallel execution is enabled for the table object and the idx1 index based on the changed DOPs.
CREATE TABLE t1(c1 INT, c2 INT, c3 INT) PARALLEL 8;
CREATE INDEX idx1 ON t1(c1);
ALTER TABLE t1 PARALLEL 4;
ALTER INDEX idx1 PARALLEL 2;