Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> how to boost a slow update query

how to boost a slow update query

From: <vuht2000_at_yahoo.com>
Date: 30 Apr 2004 11:25:28 -0700
Message-ID: <c9cd729c.0404301025.74d64036@posting.google.com>


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 Received on Fri Apr 30 2004 - 13:25:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US