All Forums Database
addsmiles 13 posts Joined 01/12
02 Feb 2012
Fastest way to update a table joining to a huge table

I was wondering what is the most optimum/fastest method to carry out an update under the following scenario. What we have now is working fine, but with production volumne can create a problem.

Need to update table 1 (approx 250-500k rows) based on whether or not a record is present in table 2 (staggering 5 billion rows). The join between the tables is on 3 columns which together form the PI in both the tables.

If a record exists in TABLE 2 then

UPDATE T1

FROM TABLE1 T1, TABLE2 T2

SET T1.FRUIT = T2.FRUIT

WHERE T1.DATE = T2.DATE

AND T1.ID = T2.ID

AND T1.ID2 = T2.ID2

If a matching record is not found in table 2

UPDATE TABLE1 T1

SET T1.FRUIT = T1.DEFAULT_FRUIT

WHERE NOT EXISTS

(SELECT 1

FROM TABLE1, TABLE2

WHERE T1.DATE = T2.DATE

AND T1.ID = T2.ID

AND T1.ID2 = T2.ID2)

We can combine the above two queries if that would help.

Appreciate your help on this. Thanks.

ulrich 816 posts Joined 09/09
02 Feb 2012

If both tables have the same PI Update 1 should be OK.

I don't know your processes or if this a one time task. When you build the table 1 can't you set fruit to defaul_fruit?  In this case you would only need to run the update 1.

If this is not possible it might even be faster to do a unconditional Update on table 1 which sets FRUIT = T1.DEFAULT_FRUIT and run update 1 afterwards as this will only update 250-500k rows...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

addsmiles 13 posts Joined 01/12
02 Feb 2012

First of all thanks so much for such a prompt response.

Then thanks again for the simple and effective suggestion of carrying out second update first. That actually might work out :))) I will definitely explore this possibility.

Sometimes the solution lies beyond teradata concepts :)

-PT

robpaller 159 posts Joined 05/09
06 Feb 2012

I would suggest looking at the MERGE statement in the Teradata Manuals. It will allow you to manage both the INSERT and UPDATE actions (UPSERT) whether the matching record exists in the table in a single statement. It is also a block level operation that may yield you improved performance over the UPDATE operator which operates as a row level operation.

addsmiles 13 posts Joined 01/12
08 Feb 2012

Thanks Rob. Will definitely look at the suggestion you made.

itsmeabhi99 6 posts Joined 08/11
08 Feb 2012

If both the table have same PI then use MERGE UPDATE. Nothing faster than that.

You must sign in to leave a comment.