What is the Teradata Primary Index?
The Teradata Primary Index hashes column values to distribute rows through the table map. Distribution can be skewed even when index values are unique.
Part of the Teradata Physical Design and Data Modelling guide.
An AMP is designated to oversee all corresponding operations by utilizing a Primary Index assigned to each table row. This index may be classified as UNIQUE or NON-UNIQUE. The most crucial attribute of the Primary Index is its ability to facilitate parallel execution of all activities by Teradata, provided it is configured correctly.
The Primary Index and Primary Key of a table are not interchangeable concepts, despite their potential for similarity. While the Primary Index is responsible for distributing data, the Primary Key serves to identify an object uniquely.
A primary key can be a candidate for the Primary Index, but uniqueness does not guarantee perfectly even distribution across Access Module Processors (AMPs).
The Teradata Hashing Algorithm
A hashing function processes every row added to a table by deriving the ROWHASH, represented by a 32-bit INTEGER value, from the values of all primary index columns. Its design allows for the order of column processing to be inconsequential.
hash(a,b) = hash(b,a)
The hash bucket derived from the row hash is assigned to an AMP through the table map. An AMP is a logical processing unit; physical CPU, memory and storage arrangements depend on deployment.
A row hash is not a unique row identifier. Hash collisions and duplicate PI values require a uniqueness value, and partitioned row identifiers also include partition information. The actual key values must still be compared when locating a row.
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 bookIf the Teradata system is not reconfigured, the assignment of ROWHASH to AMP will remain consistent. The same input values will always correspond to the same ROWHASH. Nevertheless, the hashing algorithm may occasionally lead to hash collisions, where different input values are mapped to the same ROWHASH, thereby diminishing performance when they occur in large numbers. Despite this, the likelihood of a hash collision occurring is negligible.
I have been frequently asked about the impact of input column types, specifically character versus integer. It is worth noting that character columns can lead to numerous hash collisions and duplicate hash values. For a more comprehensive understanding of this topic, I recommend reading our book, which provides in-depth explanations.
Selection of the Teradata Primary Index
Three factors influence the selection of the optimal Primary Index.
- A Suitable Access Path
- Even Distribution of Rows across all AMPs
- Low Volatility of the Primary Index Columns
An efficient access path guarantees optimal performance when querying and joining tables. Our aim is to minimize the number of data blocks read during retrieval. Moreover, we strive to minimize the effort required for preparing joins. Ideally, joined tables’ primary index and row partitioning should be identical to eliminate the need for join preparation.
The second primary index criterion is achieving optimal parallelism through the even distribution of rows.
Sometimes, it is not possible to optimize for both goals simultaneously. We may need to prioritize designing the primary index for fast access, even if the data distribution is suboptimal, or vice versa. Awareness of this reality and the ability to manage the associated drawbacks is crucial.
A PI update reevaluates placement and may move the row to another AMP; a new value can also map to the same AMP. Measure the cost and avoid unnecessary changes.
When choosing Primary Index Columns, we must consider that certain data types, such as BLOB, CLOB, XML, and JSON, are not permissible.
Different data types can result in distinct hash values, despite appearing identical upon initial inspection. As an illustration:
SELECT HASHROW('3') AS CHARACTER_HASH , HASHROW(3) AS INTEGER_HASH;
| CHARACTER_HASH | INTEGER_HASH |
| 9A0F8DF8 | 6D27DAA6 |
Different data types may produce different row hashes; some types are hash-compatible.
Compatible join expressions and table maps can permit local joins. Matching types alone does not guarantee no redistribution for every query.
Some of Teradata’s built-in data types are hash-compatible, meaning different data types can generate the same hash value for a given input value. This is because the hashing algorithm operates on the internal binary representation of the input value. Whole-number data types, such as BYTEINT, SMALLINT, INTEGER, and BIGINT columns, and fractional-number data types without a fractional part (e.g., DECIMAL(n,0) columns), produce identical hash values. Similarly, the DATE data type is encoded as an integer value in Teradata, resulting in the same hash value as an INTEGER. For instance:
| Data Type | Value | ROWHASH |
| BYTEINT | 99 | 9A0F8DF8 |
| SMALLINT | 99 | 9A0F8DF8 |
| INTEGER | 99 | 9A0F8DF8 |
| BIGINT | 99 | 9A0F8DF8 |
| DATE | 99 | 9A0F8DF8 |
CHAR and VARCHAR can be hash-compatible. In the audit, ‘x’ and ‘x ‘ produced the same hash; trailing spaces did not change it. Verify character-set and comparison semantics for the actual columns.
In the audited expressions, NULL, zero and the empty string produced 00000000. Treat printed hash values as observed examples, not a portable rule for every expression type.
| Data Type | ROWHASH |
| NULL | 9A0F8CC8 |
| “ | 9A0F8CC8 |
| 0 | 9A0F8CC8 |
Teradata Performance Considerations
A fully specified UPI equality lookup can provide efficient single-row access. The following benefits describe that access case, not every query involving a UPI table:
- Only one row inside one data block is accessed.
- Other query operations may still require spool.
- Only one AMP is needed.
The NUPI continues to perform well, however:
- Several rows are accessed, typically located in different data blocks.
- Duplicate row checks may occur.
- Several IOs are needed if not all rows are located in one data block.
- Teradata might use spool files.
Summary
Selecting a Primary Index requires balancing equitable distribution and expedient access. Even distribution is futile if it results in skew during the joining of rows via columns. Conversely, it is imperative to steer clear of volatility.
There is no such thing as a flawless primary index. A single table can necessitate distinct primary indexes for various workloads. Teradata addresses this issue by providing the Single Table Join Index in conjunction with an alternative primary index.
Teradata Primary Index Documentation
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

Hi Roland,
Came back here after months and saw the UI changed. Pleasant surprise 🙂
Unable to articulate on “Different workloads may require a different primary index for the same table.”
Could you please help with an example.
Regards,
Virendra
Hi,
The primary key and primary index are two different concepts. Primary keys are used in logical data modeling. A Primary key is used to identify an object uniquely. The primary index is utilized in the physical modeling process. It is used to achieve proper data distribution and to give Teradata a fast data access path. Often it’s the case that the primary key is at the same time a good primary index, as it’s unique and therefore ensures even data distribution.
In my opinion, using a surrogate key for tables such as customers always pays off in the long term. I have seen several times that a new legacy system was introduced (replacing an existing one). If you use surrogate keys, changes on the implementation side are limited to mapping the new natural keys to existing surrogate keys. Without surrogate keys, it can be a challenging task to adjust natural keys in all core databases and data marts (Of course, without a source independent data model, you still will have a hard time)
Best Regards,
Roland
Hi Roland,
many thanks for the clarification. I will consider the use of surrogate keys for such a tables.
Hello Paul
I’d like to add that it is good to think about the primary index (PI) more as a physical distribution key and do not think about PI as a logical attribute(you may have a primary key on non-PI columns). The primary index is the key to success or failure for your DWH. If a lot of your big tables have the same primary index and partitioning then it is most likely you use the great power of Teradata.
So try to find common attributes for large tables(especially if you have ions on those attributes) when you build your data model. These common attributes may be a good choice for PI( in a lot of cases it is much better to choose then PK of the tables).
Regards
Aleksei.
Thanks Aleksei. Very good input.
Hi Roland,
thanks for the article.
I have a design question. I have worked last 6 years with Microsoft BI in DWH implementations and I usually used a surrogated key (an auto-incremental int or bigint) as the primary index (clustered) for every table in the warehouse. This supposes to bring a good performance by joining tables, reduce the fragmentation problems and makes the data portable.
In some Teradata DWH designs, I have seen a mix of surrogated keys and codes. Usually, transaction tables use surrogated keys while reference tables, like customer or product type, use the natural keys, always with the “cd” suffix as naming convention, i.e., custumer_cd, product_type_cd.
In my old world, there was a primary (clustered) index on the primary key on the table. Could you help me to map my previous concepts to the Teradata World? What could be the best practice for transactions and reference tables? How is the primary key and the primary index related?
Thanks in advance and kind regards,
Paul
I just saw that you have a very helpful post about installing Teradata 15.10 Express Edition on your blog. I hope it’s ok for you if I share the link here:
https://hernandezpaul.wordpress.com/2017/01/24/teradata-express-15-10-installation-using-oracle-virtualbox/