Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 20 Apr 2005 @ 08:17:00 GMT

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

Subj:   Re: Problem with PPI Table Creation?
From:   Victor Sokovin

  One reason you would not want to define a PI as a UPI when the table is partitioned, unless all partitioning columns have been included in the PI, has to do with the overhead of enforcing uniqueness. In your example, if (a,b) were defined as a UPI, then for each insert of a new row, each partition on the AMP the row hashed to would have to be examined to see if a duplicate value for (a,b) already existed. With a large number of partitions, that overhead of probing each partition during duplicate checking could become problematic.  

I realize there could be some technical details of the current implementation of partitioning that are difficult to describe in a short message, and those details would make the statement clear. Having said that, I must confess I don't understand the global logic here.

Uniqueness checking has always been and still is an issue of concern with UPIs, regardless whether the table is partitioned or not. Now, I thought partitioning was meant as, among other things, a performance improving feature. The formal logic (not concerned with the technical details mentioned above) would imply that the uniqueness enforcement would rather benefit from partitioning than not. Am I wrong?

Of course, performance improving features bring some overhead with them. SI, for example, help improve performance of certain queries but require "maintanance" during DML on the table. Stats help the optimizer but their collection costs valuable production time, etc. Interestingly, partitioning seems to collide with UPIs. Good to know that. Even better would be to understand why.



  <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: 15 Jun 2023