15.00 - Number of Distinct Values - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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 …

unique

always 1.0.

non‑unique

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.