content_image/1008420.jpg
GLYPH<SM590000> Up to 50% reduction in CPU and elapsed time for queries that benefitted from increasing the SRTPOOL size ZPARM in Db2 12. The largest improvements were for queries that can now complete their group by or distinct processing in one sort pass because of the larger sort tree size. 

Note:  The domain of queries that benefit from this Db2 12 enhancement can be increased by increasing the size of the sort pool (SRTPOOL ZPARM). 

## 8.5.2 Reducing the length of the sort rows 

The next sort enhancement is related to reducing the length of sort rows for internal sorts. Reducing the sort row length includes the benefit that it also reduces the total amount of memory and work file space needed for sort. 

It is common that predicates that are coded in the WHERE clause are also (often redundantly) included in the SELECT list. Any redundancy in the sort key columns or data row columns (SELECT list) has a negative effect on sort performance and resource consumption. 

ORDER BY sort already removes columns from the sort key if they are covered by equal predicates in the WHERE clause. DISTINCT or GROUP BY already remove redundant columns to allow for sort avoidance. 

However, if a sort cannot be avoided for DISTINCT or GROUP BY, such redundant columns remain as part of the row to be sorted in Db2 versions before Db2 12. Starting with Db2 12, these redundant columns are removed from the sort key, as shown in Figure 8-16. 

<img src='content_image/1008420.jpg'>

Because C1 has an equal predicate in the WHERE clause, all sorted rows are ensured to contain that same value; therefore, only column C2 is needed for the (DISTINCT) sort. 

Similarly, sort avoids duplicating the sort key from the data portion in Db2 12 if they match each other. 

Sort operates on a fixed-length concatenated key of the columns that are required to support the ORDER BY, GROUP BY, DISTINCT sort, or sort for a join operation. The requested order of the sort key might not match the order of the columns in the SQL statement’s SELECT list. 

When the sequence of columns in the sort match the leading columns in the SELECT list and contains only fixed-length columns in Db2, sort does not replicate the key. Instead, it uses the data portion for the sort, which can result in a dramatic reduction in the length of the sort row. 

