15.00 - Case Study 1: Fewer Distinct Primary Index Values Than AMPs - Teradata Database

Teradata Database Design

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

Case Study 1: Fewer Distinct Primary Index Values Than AMPs

This study defines a NUPI on parent_cust_number, the foreign key. There are only five distinct values for this attribute in the entire customer table.

Never define a primary index on a column that has fewer distinct values than the number of AMPs in the system unless no other columns are available. Because only five of the 65,536 available hash buckets are used for this table, the very best distribution you can hope for is that each of those five buckets is assigned to a different AMP.

The illustration shows an example of such a best case distribution of rows.