Parallel Sorts

The optimizer can use parallel sorts for any query. Parallel sorts can occur both when the query executes on a single coserver and when the query executes across multiple coservers.

Query Execution on a Single Coserver

When a query executes on a single coserver, the optimizer can allocate parallel sort threads in the following circumstances:

• Data that the query requires is fragmented across dbspaces.
• Multiple processors are available for this query, and you specify multiple CPU virtual processors in the NUMCPUVPS configuration parameter.
• Adequate amounts of memory are available for the sort.
• Adequate amounts of temporary space are available if the sort overflows memory.
• The COMPUTE_QUOTA environment option has not been enabled for the session with the SET ENVIRONMENT statement.

Query Execution on Multiple Coservers

Parallel sorts occur even when PDQPRIORITY is not set. If the data to be sorted resides on different coservers, the optimizer creates one sort thread for each CPU virtual processor unless the SET ENVIRONMENT COMPUTE_QUOTA has been set to ON for the session.

The optimizer can use parallel sorts for any query when the following conditions occur:

• The data to be sorted resides in dbspaces on different disks across multiple coservers.
• You specify a multiple number of CPU virtual processors.

  If you specify a value of 1 for the NUMCPUVPS configuration parameter, but you have multiple coservers configured on different nodes, the optimizer can execute one sort thread per coserver.

• You specify enough memory to perform the sort.

  The setting of PDQpriority determines the amount of memory available for a sort. When PDQpriority is 0, the maximum amount of shared memory that the optimizer allocates for a sort is about 128 kilobytes per sort instance. This amount of sort memory enables one sort thread per coserver.

  For information about memory required for sorting, refer to Estimating Sort Memory.

Other Sort Operations

Parallel sorts are not limited to queries with a PDQpriority value greater than 0. Other database operations, such as UPDATE STATISTICS and nonfragmented or detached index builds, also use parallel sorts.

When PDQPRIORITY is greater than 0,queries and other SQL statements with sort operations benefit both from additional parallel sorts.