Updating Huge tables with SQL

If you have a query that updates a huge table with values from a smaller table, most likely the query is not going to come back with a reasonable cost.

Ex:
update T1
set c1 = ( select c2 from T2 where T2.key = T1.key )
where exists ( select c2 from T2 where T2.key = T1.key )

Reason for the huge cost: For every row we want to update in T1, oracle is going to go check if the row exists in T1, and when it finds it, it will go look for the row T1 to update. So the bigger the table T1, the more expensive the query. And, if you remove the EXISTS condition, the rows in T1 that have no match in T2 will be set to NULL.

The query can be re-written this way:

UPDATE (SELECT t1.col1 t1_y, t2.col1 t2_y
FROM table1 t1, table2 t2
WHERE t1.col2 = t2.col2)
SET t1_y = t2_y;

Point to be noted - you need to have a primary key assigned on T2, else this update will not work.

[source: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:288016031632]