ASA SQL User's Guide
Query Optimization and Execution
Query execution algorithms
Grouping
The hash group by algorithm creates an in-memory hash table of group rows. As rows are read from the input, the group rows are updated. If the hash table doesn't fit into memory, the input is partitioned into smaller work tables which are recursively partitioned until they fit into memory. If there is not enough memory for the partitions, the optimizer discards the interim results from the hash group by, and uses an indexed group by algorithm. The optimizer avoids generating access plans using the hash group by algorithm if it detects that a low memory situation may occur during query execution.
The hash group by algorithm works very well if the groups fit into memory, irrespective of the size of the input.
The hash group by algorithm computes all of the rows of its result before returning the first row, and can be used to satisfy a fully sensitive or values sensitive cursor. The results of the hash group by must be fully materialized before returning from the query. If necessary, the optimizer adds a work table to the execution plan to ensure this.