Teradata 8-AMP环境下唯一主键表的数据插入分布机制问询

Great question! Let’s break down exactly how your 1000 employee records (with unique Employee_no values 1-1000) get distributed across your 8 AMPs, especially since you’re using a Unique Primary Index (UPI) on Employee_no.

Core Mechanism Overview #

Teradata’s distribution logic for UPI tables relies on two key steps: rowhash calculation and AMP mapping. Here’s the play-by-play:

1. Calculate the Rowhash
   For every Employee_no value, Teradata runs it through its proprietary built-in hash function. This generates a 64-bit numerical value called a rowhash. Even though your Employee_no values are sequential and unique, the hash function is designed to scramble these values so they don’t follow a predictable pattern.

2. Map Rowhash to an AMP
   Teradata uses an internal hash map to map each rowhash value to a specific AMP. For systems with a small number of AMPs (like your 8-AMP setup), this effectively acts like a uniform distribution—think of it as the rowhash being "modded" by the number of AMPs, though the actual mapping is more robust to avoid hotspots.

Distribution for Your 1000 Records #

Since your Employee_no values are unique and Teradata’s hash function is optimized for even data distribution:

• You’ll end up with exactly 125 records per AMP (1000 / 8 = 125). The hash function ensures that sequential values don’t cluster on a single AMP—instead, they’re spread evenly across all 8.
• Hash collisions (where two different Employee_no values generate the same rowhash) are extremely unlikely here. With only 1000 unique values, the probability of a collision is negligible. If one did occur, both records would land on the same AMP, but this wouldn’t throw off the overall balance.

Key Notes About UPI Distribution #

• UPI guarantees that each unique key maps to exactly one AMP (even if a collision happens, all matching rowhash records go to the same AMP). This is why UPIs are great for fast point lookups—Teradata knows exactly which AMP to query without scanning all nodes.
• The hash function is deterministic: the same Employee_no will always generate the same rowhash, so reinserting the same value will always land on the same AMP.

内容的提问来源于stack exchange，提问作者Dark Knight