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 7-7 shows how to combine the index and parallel keywords to obtain serial or parallel scans.
Use: |
To specify a: |
|---|---|
(index tablename parallel N) |
Parallel partition scan |
(index index_name parallel N) |
Parallel index scan |
(index tablename parallel 1) |
Serial table scan |
(index index_name parallel 1) |
Serial index scan |
(parallel N) |
Parallel scan, with the choice of table or index scan left to the optimizer |
(parallel 1) |
Serial scan, with the choice of table or index scan left to the optimizer |
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.
The select statement is used for an update or insert.
