Archives of the TeradataForum

Message Posted: Wed, 22 Feb 2006 @ 16:32:45 GMT

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

Primary indexes are used for 2 things in Teradata. First and foremost is data access. The secondary purpose is for data distribution across the system.

Timestamps are often added to a Primary Key in order to guarantee uniqueness of the data row (a requirement of a Primary Key) and they will never be null (another requirement of a Primary Key). However, this column will never be used to join to other tables in your system.

In the case of the example stated previously, acct_id would be paired with the timestamp of the creation of the row in the warehouse to form the primary key. The only time the timestamp would be useful in a join would be if there were duplicate account numbers in the warehouse and the timestamp was there to allow us to find the appropriate account row.

In that case, an inequality would be introduced on the column as a filter along with the join to the appropriate transaction(s) via the acct_id.

One helpful exercise is to decompose multicolumn keys down to a column or set of columns that can be used for equal joins on the tables within a given subject area (ie. customer, account/transaction) and still not have too many duplicates on that column set. This becomes the primary index for the tables within the subject area. One thing you will find is that any timestamp value will not be a good column to choose for being part of a multicolumn PI.

However, stripping a date off of the timestamp might actually be a good choice to introduce in order to create a decent partitioning value for a PPI table. This will often be helpful to group transactions for a join back to a particular account and should lead to partition elimination within Teradata.

I hope this is what you were looking for.

Jeff Pankow

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