Archives of the TeradataForum

Message Posted: Fri, 05 Jan 2007 @ 10:03:05 GMT

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

Hi Amit,

What I think you're saying is that all of your tables are going to have a UPI. If my understanding is correct, yes I think you will find this causes a performance problem.

When all of your tables have UPI's the chances of being able to directly join (i.e. a PI:PI, amp-local join) two tables goes down. This means that most of your joins require redistribution or duplication of data. Obviously this is not a real problem if the data volumes are very small, but there will be some queries which are joining large volumes of data.

I've seen this in one other site and they drove their Bynet usage very high (IMHO the Bynet is completely over-engineered for a Teradata system - that's not a bad thing - and you should never be able to drive it really hard). They had defined all of their tables to have UPI's and they had significant performance problems. We changed a lot of their larger tables to have NUPI's and their performance problems largely disappeared.

As with most features on Teradata (or any other DBMS for that matter), UPI's have their place and function, but they can be mis-used.

One thing that I'm not sure of is how using PPI 's might affect the above. I think that if the selection criteria was able to eliminate partitions then you might get less data being redistributed/duplicated, but there will still be some. I'm not sure.

Cheers,

Dave

Ward Analytics Ltd: Information in motion ( www.ward-analytics.com )

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