Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Query Optimizer » Query optimization and execution » Query execution algorithms » Grouping algorithms


HashGroupBy algorithm (GrByH)

HashGroupBy builds an in-memory hash table containing one row per group. As input rows are read, the associated group is looked up in the work table. 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.

HashGroupBy 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.

HashGroupBy can be executed in parallel.

HashGroupBy 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 HashGroupBy 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 HashGroupBy, and uses an internal low memory strategy instead.

The amount of memory that can be used by a HashGroupBy operator is dependent upon the multiprogramming level of the server, and the number of active connections. See Threading in SQL Anywhere, and Setting the database server's multiprogramming level.