Archives of the TeradataForum

Message Posted: Wed, 14 Dec 2011 @ 20:40:06 GMT

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

Sravan,

Please look up past posts about this subject. There is also a whole section on Primary index choice in Teradata documentation (database design and Performance management are the ones I would start with...)

THE PI choice does not need to be unique. IT should be either unique or nearly unique for even distribution.

Nearly unique is really the number of rows per PI value should fit with within 1-2 blocks for optimal performance.

If you don't want to calculate it pick a number like 5000 rows per PI value.

When you have multiple choices for a primary index (almost always the case), you need to look at data access to choose the best PI.

There are two types of access: primary index retrieval and Join Primary index retrieval is when a query specifies an equal constraint on all columns in the Primary index. This is rare in Data warehousing.

Join access is when you are joining two tables together. You want the primary indices to be on the columns used in the join predicate to avoid data redistribution. You usually want to choose Primary index based on JOIN ACESS as long as this choice meets the unique or nearly unique criteria.

Having multiple columns can allow for a more even distribution of data. It does not hurt join performance much as the join is done on the HASH values. However, it does have to compare the actual data so the more bytes in the PI columns the more overhead. SO here you have to look at your situation. Multiple factors that I don't have time to state (and may get wrong).

On PI Retrieval access, more columns usually means fewer queries will have all the PI columns with equal conditions in the where. Thus not many PI access queries. However, this depends on the site.

Hope this helps.

Thanks,

Robert Diehl
Travelocity Data Warehouse DBA

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