Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 23 Dec 2006 @ 18:05:28 GMT


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


Subj:   Re: MLoad and PPI question
 
From:   Michael Larkins

Andy:

You have not told us much about your partitioning column. So I don't know if it is part of your PI or not (UPI vs NUPI). It sort of sounds like it is not, if you are changing it. Regardless, if you change it, Teradata would be required to delete the row from its current block and inserted it into a different block. Therefore, it acts more like a primary key in other databases, where in Teradata it is used to sequence the rows within the table. Without the partitioning column being specified, Teradata would be required to do a partition scan on every partition to find the row you are looking for: but since it is probably a NUPI, you could end up changing every row in every partition that had the same PI value - probably not what you wanted either.

>From your comment regarding duplicate sales rows, it sounds like you are doing an UPSERT and not an UPDATE in Mload. Otherwise the UPDATE would fail and that update data would end up in the UV table as a "missing update row." From the perspective of an UPSERT it is doing exactly what you are telling it to do. Like the patient told the doctor, "It hurts when I do that." Doctor's reply, "Don't do that!"

Since the row would be in the UV table on a missing UPDATE, you could join it to your target table to impliment these "on occasion" changes using SQL - just a thought

Otherwise, if your partitioning value is going to change (not a good idea by the way), you will probably need to do a DELETE and then an INSERT. I might be wrong on this and if I am my bet is someone else is going to tell us so. However, dropping the UPSERT portion of your Mload will eliminate the duplicate sales issue you mention. Mload can only do what you script it to do - so fix your script.


Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor



     
  <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