Re: how to boost a slow update query

On 30 Apr 2004 11:25:28 -0700, vuht2000_at_yahoo.com wrote:

>Guys,
>I have 2 tables, table1 (id, val1, val2,val3) and table2 (id,
>val1_new), each with exactly the same number of rows (~200,000) and
>same values in id columns. I want to update a part of table1 which has
>val2=val3 (~ 300 rows), replace val1 of table1 with val1_new of
>table2. Both tables have indices on id.
>I use this query:
>update table1 a set a.val1 =
>(select val1_new from table2 b where a.id=b.id)
>where a.val2=b.val3;
>
>it runs in about 3 mins just to update 300 rows, while a query with
>the same purpose in mysql runs in 10sec
>
>I also run a slightly different version:
>
>update table1 a set a.val1 =
>(select val1_new from table2 b where a.id=b.id and a.val2=a.val3);
>
>but same performance
>
>any suggestion for improvement?
>
>Thanks,
>Tam

1 Run explain plan and see what happens
2 Your update statements don't make sense logically, and I assume this why you are getting an inefficient plan. In the first variant you are referring in the outer (update) block, to a value in the subquery block. It also contradicts your predicate a.val2= a.val3
In the second variant you are referring, out of the blue to main query in the subquery.
Rethink which statement is logically correct, to me it looks like you need to take a.val2=a.val3 out of the subquery and put in the where clause of the update statement.
Then start tuning.

--
Sybrand Bakker, Senior Oracle DBA