Why would you choose a Non-Unique Primary Index (NUPI) instead of a Unique Primary Index (UPI)?
-
A NUPI requires less disk space to store the data row.
-
A NUPI may be more effective for query access and joins.
-
A NUPI query amplifies the optimization process in the PE.
-
A NUPI provides better data distribution
A Non-Unique Primary Index (NUPI) can be more effective than UPI for query access and joins because it provides better data distribution when the natural key doesn't distribute evenly, and it avoids the overhead of uniqueness enforcement. NUPI requires the same disk space as UPI and provides no better distribution. Option C is nonsensical terminology.
In Teradata, a Non-Unique Primary Index (NUPI) is often preferred over a Unique Primary Index (UPI) when the column(s) chosen are frequently used to join large tables together: because Teradata distributes and locates rows by hashing the Primary Index value, using the natural join column as a NUPI means matching rows from both tables hash to the same AMP, enabling efficient AMP-local joins without redistribution — even though it permits duplicate values (hence 'non-unique'). This join/access efficiency benefit is the standard reason cited for choosing NUPI over UPI, rather than disk-space savings, PE-side query optimization amplification, or a blanket claim about distribution (NUPI can actually cause skew if values aren't well distributed, unlike a UPI which guarantees even distribution).