Re: Speedup the dml ??

From: <michael_bialik_at_my-deja.com>
Date: 2000/04/14
Message-ID: <8d7k6h$p6q$1@nnrp1.deja.com>#1/1

Hi.

 I think that in your case Oracle performs FULL table scan for table A  ( 600K rows ) and for each one it's making SELECT from table B ( by  unique index scan ). In 540K cases it's not necessary, because no  corresponding row exists in table B.

 Try doing it the other way :
 BEGIN
   FOR b_rec IN ( SELECT * FROM b ) LOOP

     UPDATE a SET c5 = b_rec.c5
     WHERE
      c1 = b_rec.c1 AND c2 = b_rec.c2 AND
      c3 = b_rec.c3 AND c4 = b_rec.4;

   END LOOP;
   COMMIT;
 END;
 If you are working with 8i - use array and BULK COLLECT/BULK BIND  options.
 If you are using 3gl ( ProC, ProcCobol, etc. ) - use array processing.

 HTH. Michael.

 If possible - post results ( before and after ).

In article <MPG.13617895ac05bf43989683_at_news.gcn.net.tw>,   feib <agi_at_feib.com.tw> 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).
>
> 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 );
>
> Are there any better statement ( like EXISTS,or any hint to
 speedup) ??
> Any ideas ??
>
> Rgds,
> Agi Chen
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 14 2000 - 00:00:00 CDT