|
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.
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.
The Hash Group By algorithm can be executed in parallel.
The Hash Group By algorithm works very well if the groups fit into memory, regardless of the size of the input. 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. 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. If there is not enough memory for the partitions, the optimizer discards the interim results from the Hash Group By, and uses the Indexed Group By algorithm instead.
Memory governor limits are dependent upon the server's multiprogramming level and the number of active connections. See Threading in SQL Anywhere, and Setting the database server's multiprogramming level.
The Indexed Group By algorithm builds a work table containing one row per group. As input rows are read, the associated group is looked up in the work table using an index. The aggregate functions are updated, and the group row is rewritten to the work table. If no group record is found, a new group record is initialized and inserted into the work table.
When the Indexed Group By algorithm is needed due to low memory, a performance counter is incremented. You can see this using the QueryLowMemoryStrategy database or connection property, in the QueryLowMemoryStrategy statistic in the graphical plan (when run with statistics), or in the Query: Low Memory Strategies counter in the Windows Performance Monitor. See Performance Monitor statistics.