Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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 DBAReceived on Fri Apr 30 2004 - 15:53:37 CDT