Archives of the TeradataForum
Message Posted: Wed, 06 Feb 2002 @ 16:27:17 GMT
Recently I have had many discussions with co-workers on the subject of running an UPDATE to a table using data from another table. Most of ourtables have more than 1,000,000 rows and they are trying to update anywhere from 25% to 75% of the data rows are between 2 to 5 fields actually being updated. If we run an UPDATE, such as:
UPDATE Table1 SET Col1 = 'a', Col2 = 34 WHERE Col3 = 'Apples';
We encounter no major issues. The UPDATE eventually finishes and everyone is happy. If we instead say something like this:
UPDATE Table1 SET Col1 = Table2.Col4, Col2 = Table2.Col5 WHERE Col3 = 'Apples' AND Table1.Col6 = Table2.Col6;
The query never finishes or will run for hours or days and then we abort it, etc. I understand UPDATEs use overhead (Transient Journal) and our little two node 4850 may not be powerful enough to handle UPDATEs. I have heard from some NCR/Teradata folks that we should not update more than 10,000 records at a time. I am convinced we will have to run more DELETE/INSERT processing and get away from UPDATEs, however, I need more information on the subject as to other methods or better practices.
Thank you for all your time and help on this subject in advance.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|