Archives of the TeradataForum

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

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

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>>