Tips for grouping hash implementation

The time required to receive the first results for this implementation will most likely be longer than other grouping implementations because the hash table must be completely built and populated first. Once the hash table is completely populated, the database manager uses the table to start returning the grouping results. Before returning any results, the database manager must apply any specified grouping selection criteria or ordering to the summary entries in the hash table.

The grouping hash method is most effective when the consolidation ratio is high. The consolidation ratio is the ratio of the selected table rows to the computed grouping results. If every database table row has its own unique grouping value, then the hash table will become too large. This in turn will slow down the grouping hash access method.

The optimizer uses the cardinality statistic of the grouping columns to help determine the optimal size of the hash table. The hash access method is more effective when the hash table is well balanced and uses the available memory efficiently. The number of hash table partitions as determined by the cardinality directly effects the how entries are distributed across the hash and the uniformity of this distribution.

For more information, see Database Performance and Query Optimization in the Information Center Link to Information center.