Grouping optimization

Db2® for i has certain techniques to use when the optimizer encounters grouping. The query optimizer chooses its methods for optimizing your query.

Hash grouping implementation
This technique uses the base hash access method to perform grouping or summarization of the selected table rows. For each selected row, the specified grouping value is run through the hash function. The computed hash value and grouping value are used to quickly find the entry in the hash table corresponding to the grouping value.
Index grouping implementation
There are two primary ways to implement grouping using an index: Ordered grouping and pre-summarized processing.
Optimizing grouping by eliminating grouping columns
All the grouping columns are evaluated to determine if they can be removed from the list of grouping columns. Only those grouping columns that have isolatable selection predicates with an equal operator specified can be considered. This guarantees that the column can only match a single value and does not help determine a unique group.
Optimizing grouping by adding additional grouping columns
The same logic that is applied to removing grouping columns can also be used to add additional grouping columns to the query. Additional grouping columns are added only when you are trying to determine if an index can be used to implement the grouping.
Optimizing grouping by using index skip key processing
Index Skip Key processing can be used when grouping with the keyed sequence implementation algorithm which uses an existing index. It is a specialized version of ordered grouping that processes few records in each group rather than all records in each group.
Optimizing grouping by removing read triggers
For queries involving physical files or tables with read triggers, group by triggers always involve a temporary file before the group by processing. Therefore, these queries slow down.
Grouping set optimization
The optimizer uses all the previously mentioned grouping optimizations for individual grouping sets specified in the query.

Parent topic: Processing queries: Overview