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

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
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.