The parallel and degree_of_parallelism extensions to the from clause of a select command allow users to restrict the number of worker processes used in a scan.
For a parallel partition scan to be performed, the degree_of_parallelism must be equal to or greater than the number of partitions. For a parallel index scan, specify any value for the degree_of_parallelism.
The syntax for the select statement is:
select...
[from {tablename}
[(index index_name
[parallel [degree_of_parallelism | 1]]
[prefetch size] [lru|mru])],
{tablename} [([index_name]
[parallel [degree_of_parallelism | 1]
[prefetch size] [lru|mru])] ...
Table 20-2 shows how to combine the index and parallel keywords to obtain serial or parallel scans.
To specify this type of scan: |
Use this syntax: |
|---|---|
Parallel partition scan |
(index tablename parallel N) |
Parallel index scan |
(index index_name parallel N) |
Serial table scan |
(index tablename parallel 1) |
Serial index scan |
(index index_name parallel 1) |
Parallel, with the choice of table or index scan left to the optimizer |
(parallel N) |
Serial, with the choice of table or index scan left to the optimizer |
(parallel 1) |
When you specify the parallel degree for a table in a merge join, it affects the degree of parallelism used for both the scan of the table and the merge join.
You cannot use the parallel option if you have disabled parallel processing either at the session level with the set parallel_degree 1 command or at the server level with the parallel degree configuration parameter. The parallel option cannot override these settings.
If you specify a degree_of_parallelism that is greater than the maximum configured degree of parallelism, Adaptive Server ignores the hint.
The optimizer ignores hints that specify a parallel degree if any of the following conditions is true:
The from clause is used in the definition of a cursor.
parallel is used in the from clause of an inner query block of a subquery, and the optimizer does not move the table to the outermost query block during subquery flattening.
The table is a view, a system table, or a virtual table.
The table is the inner table of an outer join.
The query specifies exists, min, or max on the table.
The value for the max scan parallel degree configuration parameter is set to 1.
An unpartitioned clustered index is specified or is the only parallel option.
A nonclustered index is covered.
The query is processed using the OR strategy.
For an explanation of the OR strategy, see “Access Methods and Costing for or and in Clauses”.
The select statement is used for an update or insert.
