Thanks Sybrand,
there's a type, it should be "...where a.val2=a.val3"
the plan shows that both tables have full table scan. So which columns
should I place index on and/or how to force oracle to use them to
avoid fts?
regards,
Tam
Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl> wrote in message news:<9ue590teg6n2c7u98ag757roaeq4rusqu9_at_4ax.com>...
> 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.
Received on Mon May 03 2004 - 10:21:40 CDT
