Usage of Nulls | Database Design | Teradata Vantage - 17.10 - Hashing on Nulls - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
User Guide
Publication ID
English (United States)

Even though nulls have no external value, they do have an internal value that can be processed by the Vantage hashing algorithm. If a NUSI for a table permits nulls, there is an increased probability of an uneven distribution of the rows for that table across the AMPs of a system because all rows having a null primary index hash to the same AMP.

If the number of rows having a null primary index is sufficiently large, then significant skew occurs, making efficient parallel processing difficult to achieve. This is not a problem for UPIs because there can be no more than one null UPI per table.

Although indexes are not a logical concept, and therefore are not part of the relational model, Teradata primary indexes are built from in-row values, so a null UPI provides a mechanism for what is effectively a potential duplicate row to be stored in the system as long as the column set remains null.