Suggesting a degree of parallelism for a query

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 3-3 shows how to combine the index and parallel keywords to obtain serial or parallel scans.

Table 3-3: Optimizer hints for serial and parallel execution

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: