To comprehend the issue of Teradata hash collisions, I will briefly explain how rows are allocated. If you are unfamiliar with Teradata Architecture or require a refresher, I suggest reading the following article beforehand:
As you know, a hashing algorithm distributes a table’s rows to the AMPs.
The Foundations
The hashing algorithm accepts one or multiple column values as input and outputs a 32-bit integer known as ROWHASH.
Two crucial attributes of this hashing function are vital when discussing hash collisions:
- The same input values always produce the same ROWHASH
- Different input values can generate the same ROWHASH
The second feature that piques our interest is the ROWHASH, a 32-bit integer, as previously stated.
A 32-bit hash has 2^32 possible values, but collisions can occur far below that row count through the birthday effect. Four billion is the hash-space size, not a collision interval.
Teradata hash collisions are why it is insufficient to know the ROWHASH to locate a row in a data block.
Therefore, every time a row is searched, the ROWHASH and the searched value must be passed to the AMP in the parsing engine’s message.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookIn addition, a 32-bit integer is appended to the Rowhash, which uniquely identifies the row. This 32-bit integer, together with the ROWHASH, is called ROWID.
The first 16 or 20 bits (depending on the Teradata System) of the ROWHASH are the so-called Hash Bucket. The hash bucket determines which entry in the hash map is responsible for this row. Finally, the hash map shows the target amp.
A Case Study On Teradata Hash Collisions
It is important to exercise caution when character columns are designated as the primary index, despite the effectiveness of the Teradata Hashing Algorithm in ensuring proper distribution and preventing hash collisions. Consider the following example.
We first create two tables. The primary index of one table is of data type BIGINT. The second table has a primary index of data type VARCHAR(30):
CREATE TABLE DWHPRO.COLLISION_YES
(
PK VARCHAR(30) NOT NULL
) PRIMARY INDEX (PK);
CREATE TABLE DWHPRO.COLLISION_NO
(
PK BIGINT NOT NULL
) PRIMARY INDEX (PK);
The two tables are populated four times with varying SAMPLE sizes of 40 million, 80 million, 120 million, and 160 million rows. Hash collisions are then checked after each load.
INSERT INTO DWHPRO.COLLISION_NO
SELECT ROW_NUMBER() OVER (ORDER BY 1) FROM DWHPRO.A_HUGE_TABLE
SAMPLE <n>;
INSERT INTO DWHPRO.COLLISION_YES
SELECT TRIM(ROW_NUMBER() OVER (ORDER BY 1)) FROM DWHPRO.A_HUGE_TABLE
SAMPLE <n>;
SELECT COUNT(*) FROM
(
SELECT HASHROW(PK) AS HashValue ,COUNT(*) X FROM DWHPRO.COLLISION_NO
GROUP BY 1 HAVING X > 1
) X;
SELECT COUNT(*) FROM
(
SELECT HASHROW(PK) AS HashValue ,COUNT(*) X FROM DWHPRO.COLLISION_YES
GROUP BY 1 HAVING X > 1
) X
This is the query result:
| Rows | BIGINT | VARCHAR(30) |
| 40 Mio | 0 | 213481 |
| 80 Mio | 0 | 371526 |
| 120 Mio | 0 | 824160 |
| 160 Mio | 0 | 3133779 |
BIGINT is not collision-free. An independent fixture had 200,000 distinct BIGINT values and 199,993 distinct hashes; 3608747927919672994 and 3899200227754428030 both hashed to D5328038. A separate sequential fixture with no collisions does not prove the general claim.
The case is desperate for a VARCHAR(30) primary index:

Conclusions
The historical 120-million-row table reports 824,160, not the prose claim of over three million. The query counts hash buckets with multiple rows, not colliding rows or pairs; repeated identical keys must be distinguished from different-key collisions.
Teradata hash collisions waste valuable CPU time, complicating searching for data and storing rows in data blocks. While this may not initially be a concern for a few collisions, the impact on CPU performance will eventually become evident.
It is important to consider the design of your Primary Index carefully. Utilizing surrogate keys in your data warehouse can be a beneficial approach.
Clear or recreate each test table between sample sizes if comparing independent loads. The current fixture disproves collision freedom but is not a production collision-rate or CPU benchmark.
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.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book