DWHPro Insights

Optimal Performance by Avoiding CHARACTER Columns in the Primary Index

By Roland Wenzlofsky ·

Optimal Performance by Avoiding CHARACTER Columns in the Primary Index

The Teradata Primary Index and Hash Collisions

Primary-index data types, character sets and collations can materially affect hashing, comparison and join conversions. Choose types for correct business semantics and compatible joins; benchmark relevant access paths before changing them.

This article addresses a performance issue with CHARACTER columns in the primary index. Although not as apparent as skew, this problem is often overlooked and remains hidden.

Assuming a Teradata system with 160 AMPs and 20-bit hash buckets, we create a table with a primary index of a CHARACTER(8) column that contains 100 million phone numbers. The 20-bit hash buckets allow for 1,048,576 unique hash buckets. The hashing algorithm is expected to provide:

  • Each hash bucket is used approx. 95 times
  • Each AMP receives approx. 625,000 rows
  • Hash collisions remain possible and expected at this scale; a 32-bit hash does not uniquely encode 100 million arbitrary inputs

We computed the DSW, AMP, and collision count for every possible input value.

The historical chart describes bucket occupancy for this particular input. Uniform random hashing has variance, not a perfect impulse distribution. A peak of 43,000 buckets is about 4.1% of 1,048,576, not 25%. The original 100-million-row workload was not reproduced.

teradata primary index

Upon closer examination, it becomes evident that the equitable allocation of rows across the AMPs is not the underlying issue. Each AMP receives an almost identical number of rows, ranging from 623,000 to 627,000. Rather, the problem lies in generating hash collisions on all AMPs, as demonstrated in the subsequent graph.

teradata hash collisions

The algorithm produced around 15,000 hash collisions per 160 AMPS. Most of these collisions result from three or four primary index values being linked to the same hash value. This is concerning, as hash collisions have a negative impact on performance.

Let’s revisit the scenario, but this time, we store the phone numbers in a BIGINT column and employ it as the Teradata Primary Index. We create a table containing 100 million rows. The resulting graph corresponds to our expectations for the CHARACTER column.

teradata primary index

The historical numeric sequence reportedly produced bucket counts of 93–98 and no duplicate hashes in that sample. This does not imply collision-free BIGINT hashing. A separate audit fixture of 200,000 distinct random BIGINT values produced 199,993 distinct row hashes. It disproves the universal claim but is not a production collision-rate benchmark.

Summary

Using CHARACTER columns in the primary index may result in numerous hash collisions. While the contents of such columns are usually limited, this may not be a concern. Nevertheless, significant performance issues may arise in exceptional scenarios where millions of distinct values are present in the primary index column.

Do not change a primary-index data type solely from these historical charts. Numeric conversion can lose leading zeros or alter comparisons, and incompatible types can change join plans. Measure skew, collisions and query cost for the actual domain before a redesign.

This article will illustrate additional factors to consider when selecting the Primary Index for optimal performance.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Get more practical data engineering advice

Join the DWHPro newsletter for practical data engineering tips, troubleshooting examples and data platform insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam