15.00 - Number of Distinct Values - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Number of Distinct Values

This number reports how many distinct values a particular primary index column contains.

With respect to NUPI behavior, the closer the ratio of distinct index column values to table cardinality is to 1.0, the better. For a UPI, this value is always 1.0 by definition.


FOR this type of primary index …

The ratio of distinct index column values to table cardinality is …


always 1.0.


best for distribution when it is close to 1.0 and usually, but not necessarily, worse the further away from 1.0 it becomes.

The evenness of the distribution of rows per value is also an important factor, with better distribution correlating with more even distributions.

With respect to space utilization, the number of distinct values should always be very much greater than the number of AMPs in the configuration to ensure that each table distributes its rows to all AMPs.

While this might not be possible to achieve for very small tables, it is an absolute necessity for anything larger.

If the number of rows per distinct value for a column is significantly larger than 1, then it might not be a good candidate for a primary index.