Re: Speedup the dml ??

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: 2000/04/15
Message-ID: <38F7A7AB.AD98E6D9@0800-einwahl.de>#1/1

First of all, your update statement would also update the rows of a which do not have a pendant in b. c5 would become null there. If this is intention - fine. If not you must add something like

...
where (a.c1, a.c2, a.c3, a.c4) in (

	select b.c1, b.c2, b.c3, b.c4
	from b

)

to your update statement.

I checked three things at my NT 4.0, SP 6a, Pentium III 650 MHz, 128 MB:

1. the performance of your update statement (without my correction): 650 s update a set c5 = ( select c5 from b where a.c1=b.c1 and a.c2=b.c2 and a.c3=b.c3 and a.c4=b.c4 );
2. my update statement with correction: 73 s SQL> update --+ all_rows 2 ( 3 select 4 a.c5 5 , b.c5 as new_c5 6 from 7 testa a 8 , testb b 9 where 1 = 1 10 and a.c1=b.c1 11 and a.c2=b.c2 12 and a.c3=b.c3 13 and a.c4=b.c4 14 ) x 15 set x.c5 = x.new_c5 16 /
3. your statement with correction: 83 s

SQL> update --+ all_rows
  2 testa a

  3  	      set c5 = ( select c5
  4  			      from testb b
  5  			    where a.c1=b.c1
  6  				and a.c2=b.c2
  7  				and a.c3=b.c3
  8  				and a.c4=b.c4

  9 )
 10 where (a.c1, a.c2, a.c3, a.c4) in (

 11  	     select b.c1, b.c2, b.c3, b.c4
 12  	     from testb b

 13 )
 14 /

60000 rows updated.

Martin

feib wrote:
>
> Hello, there
> I have two tables a,b ( a with 600000 records, b 60000 records)
> Both a and b have the same columns c1 c2 c3 c4 c5.
> I want to update a's c5 by b's c5 for the same key value c1-c4.
> So,I create two unique indexies on a(c1,c2,c3,c4) and b(c1,c2,c3,c4).
>
>
> Are there any better statement ( like EXISTS,or any hint to speedup) ??
> Any ideas ??
>
> Rgds,
> Agi Chen
>
>
>
  Received on Sat Apr 15 2000 - 00:00:00 CDT