Archives of the TeradataForum

Message Posted: Fri, 27 Jun 2003 @ 18:37:31 GMT

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

I need help evaluating a PI design.

I have a table X and a table XR (X Relationship) that is the parent child relationship of the rows in X.

Table X has a column X_Id that is the UPI for the table.

Table XR has two columns X_Id and Related_X_Id.

Originally, table XR had an NUPI on X_id but this caused skewing as there was a 1:M relationship between X_id and related_X_Id. We changed the NUPI to be Related_X_Id. The idea being that the PI would match the PI of at least one of the tables it was intended to join with, thus reducing redistribution dung query. Unfortunately, we have some cases where we are still getting a long list of NUPI chains.

We can solve both the distribution and NUPI change problem by making a UPI using either a surrogate key for the XR table or a compound Pi using both the X_Id and Related_X_Id column. This would cause at least two of the tables to redistribute in a 3 table join.

Are there other candidate solutions or can you suggest anything to check before making changes. Perhaps there are rules of thumb to follow.

Thanks

Jim

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