NewHashBucketSize - Advanced SQL Engine - Teradata Database

Purpose

Specifies the number of bits that will be used by the system to identify hash buckets after the next system initialization or reconfiguration. This setting determines how many hash buckets the system can create. A setting of 16 bits gives Teradata Database 65,536 hash buckets; a setting of 20 bits gives Teradata Database 1,048,576 hash buckets.

One goal of the Teradata Database parallel system is to distribute work evenly among the system resources (nodes, virtual processes, and storage). The number of hash buckets an AMP vproc uses is directly related to the amount of work an AMP must do. AMPs with more hash buckets manage more data, and therefore do more work than those with fewer hash buckets.

On many systems, the number of AMPs is not evenly divisible into the number of available hash buckets. Consequently, some AMPs have one more hash bucket than other AMPs. If the number of hash buckets per AMP is relatively high, the imbalance is proportionately low, and the difference in the amount of work the AMPs must do is relatively small.

However, as the number of AMPs is increased on the system, the hash buckets available to each AMP decreases. With fewer hash buckets per AMP, the effect of any imbalance in the number of hash buckets per AMP becomes proportionately greater. This results in the system operating less efficiently.

For example, an AMP using 656 hash buckets must do 1/656 or 0.15% more work than an AMP using 655 hash buckets, but an AMP using only 66 hash buckets must do 1/66 or 1.52% more work than an AMP using 65 hash buckets.

Making more hash buckets available to the system and to each AMP reduces the effects of the imbalance when some AMPs have one more hash bucket than others.

Field Group

General

Valid Settings

16 and 20 bits

Default

Changes Take Effect

After the next system initialization or reconfiguration.

Related Topics

See CurHashBucketSize.