Archives of the TeradataForum

Message Posted: Tue, 28 Jun 2005 @ 14:25:49 GMT

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

Hello,

I am an ETL architect and am attempting to understand how the UPI should be designed on a teradata warehouse table. I have a question on how rows are hashed and distributed. I did go thru the NCR Database design manuals.

Consider the following:

Parent table UPI consists of column parent_id

Child table UPI consists of columns parent_id and child_id

There is a one-to-many relationship between parent and child tables.

My observation is that the row hash value of the parent UPI will not be equal to the row hash value of the child UPI because the child table's UPI has 2 attributes. If there were a NUPI on the child table consisting of just the "parent_id", then every incoming child row would hash to the same value as the existing parent row index. The child row will then be placed on the same AMP as the parent row; and hence the ease of AMP-local join.

But if there are no indexes with same attribute composition in all the related tables, AMP-local joins cannot be guaranteed for sure. Or can they? I read lot of "explain plans" on several related table queries, and found that most of the queries did an all-AMPs scan/join even with index scenario I just described above.

Is there any technique which can help the data architect determine the hash value range if an attribute were added an existing index ? This can help the architect determine if AMP-local joins will be disturbed by adding or taking out any attribute from an index.

Table A UPI consists of "ACCT_ID" and Table B UPI consists of "ACCT_ID and SUBSCRIPTION_ID". Obviously, their UPIs are be composed of the same attributes. There is a one-to-many relationship between Table A and Table B. I find it difficult to accept that we cannot force the database to hash out rows destined for 2 different RELATED tables, to the same AMP and still distribute them evenly. How can 2 related tables have the same unique index attribute composition ?

Let me know if you understand what I am attempting to say. I can explain it better with examples.

Swapnil

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