This article presupposes your existing familiarity with the fundamental structure of a Teradata System.
As you are aware, the AMPs operate on a Teradata System.
The number of AMPs responsible for managing rows stored on their respective virtual disks varies based on the system’s size. There may be hundreds of AMPs.
As noted in my article about the Teradata High-Level Architecture, the primary goal is to distribute rows evenly among all AMPs in order to achieve parallelism.
How Does Teradata Achieve Uniform Data Distribution?
What are the methods for achieving uniform data distribution on Teradata?
It depends on the Primary Index.
The Primary Index and Primary Key are distinct concepts. The Primary Key is used in data modeling, whereas the Primary Index is a physical design principle in Teradata.
Primary Index vs. Primary Key
Please note that the following paragraph is a simplified example but should provide an adequate understanding of the general concepts.
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 bookThe Primary Index comprises table columns used as input for a highly efficient hashing algorithm. The algorithm’s output designates a responsible AMP that assumes all tasks pertaining to the corresponding row. The designated AMP stores the row on its associated virtual disk and becomes solely accountable for handling it thereafter.
Data types matter, but distinct types are not necessarily hash-incompatible. BYTEINT, SMALLINT, INTEGER and BIGINT value 1 produced the same hash in the audit. Verify compatibility for the actual expressions and character semantics.
Data Distribution Strategy
The data distribution strategy employed is both straightforward and effective. A designated portion of the data is allocated to each AMP, with the distribution of selected rows solely determined by the Primary Index. All tables are accessible on the Teradata System and are duly recorded and updated by each AMP.
Skew can make the busiest AMP a bottleneck in parallel steps. Even PI distribution improves the opportunity for parallelism but does not guarantee linear scaling or determine every DML runtime; dependencies, locks, I/O and concurrency also matter.
Teradata’s Primary Index concept facilitates direct access to data based on a hash value, similar to other hashing algorithms. Utilizing Primary Index access is the most expeditious method of retrieving rows from disks.
Changing the Primary Index
Updating PI values requires reevaluating row placement and can move a row to another AMP. It may also map to the same AMP. Avoid unnecessary PI updates, and measure their actual cost.
Primary Indexes are classified as UNIQUE or non-UNIQUE. Records that share the same content are assigned identical hash values. Additional information, such as a uniqueness value, is added to differentiate these records. However, both records are managed by the same AMP.
While the data distribution process involves additional details, the preceding description should suffice for your everyday use of Teradata.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
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
HI. I assume the primary index you want to use is not distrubuting the rows evenly across all AMPs and you run out of space on a single AMP or a few AMPs
Hi,
Can I have Primary key columns different than the Primary Index? I tried doing that and my ETL process is failing to complain about the space issues. But, I have ample space and when I make Primary Key columns the same as Primary Index, it works just fine.
I will appreciate your response or any input.
Regards
Nirav