Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Creating tables 50% to 90% of columns as unique primary index
 
From:   DWellman

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016