Archives of the TeradataForum

Message Posted: Sat, 09 Jul 2005 @ 05:43:00 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Bob:

Lets start by saying there are a couple of reasons for picking a PI:

1. even distribution of the rows across the AMPs - unique values normally do this best.

2. based on the need to quickly select 1 or more rows - a one AMP operation is good for this and requires that ALL composite PI columns are present in the WHERE clause. Primary keys in the logical design do this pretty well. I and others in the Teradata world normally recommend the avoidance of surrogate keys and use the columns to make it unique because this goes back to actual business data instead of some psuedo value with no business meaning. If you can't get it unique in 16 columns, make it a NUPI - unlike a primary key.

3. based on join processing. If this table is always joined with another table, rows can only be compared on a single AMP. If the rows do not reside on the same AMP is PERM, one or both tables must be put into SPOOL on the same AMP so they can be matched. This normally requires row temporary "redistribution" across the BYNET and increases SPOOL requirements. If distribution is not terrible, sometimes it is better to use the join columns as the PI of the tables. If you have several dimension tables, it will most likely not be possible for all of them to have the same PI. I tend to look at distribution this causes on the larger tables first because it reduces the number of redistributed rows. The downside is that these same tables can cause the largest amount of skewed data on one or more AMPs causing Hot AMP situations that slow other types of access.

Now, this is a VERY condensed version of a 2 day course on physical database design. There are a variety of discussions on this very topic in the archives of this forum and you can search through them for more details or attend one of these physical database design courses.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

	<Prev	Next>		<<First	<Prev	Next>	Last>>