Use parallel sorts

When you cannot avoid sorting, the database server takes advantage of multiple CPU resources to perform the required sort-and-merge operations in parallel. The database server can use parallel sorts for any query, not just PDQ queries. You can control the number of threads that the database server uses to sort rows.

To control the number of threads that the database server uses to sort rows, use the PSORT_NPROCS environment variable.

When PDQ priority is greater than 0 and PSORT_NPROCS is greater than 1, the query benefits both from parallel sorts and from PDQ features such as parallel scans and additional memory. Users can use the PDQPRIORITY environment variable to request a specific proportion of PDQ resources for a query. You can use the MAX_PDQPRIORITY configuration parameter to limit the number of such user requests. For more information, see Limiting PDQ resources in queries.

In some cases, the amount of data being sorted can overflow the memory resources allocated to the query, resulting in I/O to a dbspace or sort file. For more information, see Configure dbspaces for temporary tables and sort files.