Hints related to parallel execution

Hints related to parallel execution are PARALLEL, NO_PARALLEL, USE_PX, ENABLE_PARALLEL_DAS_DML, DISABLE_PARALLEL_DAS_DML, 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.

The syntax of the PARALLEL hint is as follows:

/*+ PARALLEL(n) */

The value of the n parameter in the PARALLE hint specifies the degree of parallelism (DOP). If the statement includes the SORT BY or GROUP BY clause, 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. The syntax is as follows:

/*+ 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.

The syntax of the NO_PARALLEL hint is as follows:

/*+ NO_PARALLEL ( [ @queryblock ] tablespec ) */

In the following example, the NO_PARALLEL hint overrides 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.

The syntax of the USE_PX hint is as follows:

/*+ USE_PX */

Here is an example 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;

ENABLE_PARALLEL_DML hint

The ENABLE_PARALLEL_DML hint specifies to enable parallel DML (PDML) for the current query. Its reverse hint is DISABLE_PARALLEL_DML, which specifies to disable PDML.

For more information about PDML, see Parallel DML.

Considerations

• If the/*+ENABLE_PARALLEL_DML PARALLEL(n)*/ hint is used, the system will preferentially use distributed PDML. If PDML is not supported in the current environment, the system will use concurrent write in Distributed Active Storage (DAS) for performance optimization.

• If a degree of parallelism (DOP) is forcibly set at the session level, the execution behavior is consistent with that described above.

• If the _enable_parallel_das_dml parameter, whose default value is false, is set to true by using the ALTER SYSTEM SET statement while PDML is not supported in the current environment, the system will not enable parallel write in DAS, even if the /*+ENABLE_PARALLEL_DML PARALLEL(n)*/ hint is used.

• If the _enable_parallel_das_dml parameter, whose default value is false, is set to true by using the ALTER SYSTEM SET statement and a DOP is forcibly set at the session level, using the /*+ENABLE_PARALLEL_DML PARALLEL(n)*/ hint will not enable parallel write in DAS, even if PDML is not supported in the current environment.

Syntax

/*+ ENABLE_PARALLEL_DML */

Examples

insert /*+ enable_parallel_dml parallel(8) */ into t2 select * from t1;

DISABLE_PARALLEL_DML hint

The DISABLE_PARALLEL_DML hint specifies to disable PDML. Its reverse hint is ENABLE_PARALLEL_DML, which specifies to enable PDML.

For more information about PDML, see Parallel DML.

Syntax

/*+ DISABLE_PARALLEL_DML */

Examples

insert /*+ disable_parallel_dml parallel(8) */ into t2 select * from t1;

ENABLE_PARALLEL_DAS_DML hint

The ENABLE_PARALLEL_DAS_DML hint specifies to forcibly enable parallel write in DAS for the current DML statement for performance optimization. Its reverse hint is DISABLE_PARALLEL_DAS_DML.

Considerations

• ENABLE_PARALLEL_DAS_DML must be used in combination with ENABLE_PARALLEL_DML.

• With the /*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/ hint, the system will forcibly enable parallel write in DAS and proceed based on the DOP specified by PARALLEL(n).

• If the _enable_parallel_das_dml parameter, whose default value is false, is set to true by using the ALTER SYSTEM SET statement, the /*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/ hint will become invalid. In this case, the system will not apply parallel write in DAS.

Syntax

/*+ ENABLE_PARALLEL_DAS_DML */

Examples

insert /*+ ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(10)*/ 
into t1 select * from t2;

DISABLE_PARALLEL_DAS_DML hint

The DISABLE_PARALLEL_DAS_DML hint specifies to forcibly disable parallel write in DAS for the current DML statement for performance optimization. Its reverse hint is ENABLE_PARALLEL_DAS_DML.

Considerations

• DISABLE_PARALLEL_DAS_DML must be used in combination with ENABLE_PARALLEL_DML.

• If the /*+DISABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/ hint is used, the system will disable parallel write in DAS, even if another parallel write optimization option, such as PDML, is enabled.

Syntax

/*+ DISABLE_PARALLEL_DAS_DML */

Examples

insert /*+ DISABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(10)*/ 
into t1 select * from t2;

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. The syntax of the PQ_DISTRIBUTE hint is as follows:

/*+ 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_distribution specifies the data distribution mode for the left-side table.

• inner_distribution specifies 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.

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;