Re: what is the fastest way to update a big table with more than 50000 rows and 20 columns

From: Rachel Carmichael <wisernet100_at_yahoo.com>
Date: Wed, 31 Oct 2001 06:33:42 -0800
Message-ID: <F001.003B9275.20011031054530@fatcity.com>

are all the rows in the newer table also in the older one?

you seem to be unnecessarily complicating your life. why not just do the script below. You are also forcing Oracle to read the ENTIRE larger table since you are performing a function on the id in the update statement. If there is anyway NOT to do that, that will speed things up.

       declare
       cursor c1 is
       select j.totid,j.source from join j where j.source is not null

       --join is the new table (17700 rows)
       --cs id the older one (50300rows)
       tot cs.TOTID%type;--totid is the primary key for both tables
       src cs.source%type;--source is the column to be updated
       begin
       open c1;
       loop
       fetch c1 into tot,src;
       tot:=trim(tot);
       exit when c1%notfound;
       update cs set source=src where trim(totid)=tot;
       end loop;
       commit;
       close c1;
       end;


--- yilmaz <yilmaz_at_hwajet.com.tw> wrote:


>       hi guys,

>       i have a big problem in updating an oracle 8i 1.7 database

> table with
> more than 50000 rows and 20 columns. The problem is: i have another
> table
> with approximately 17000 rows and 12 columns. The latter one holds
> newer
> data, and most of them have the same primary key as the previous one
> (16000). Now, i want to update the older table with newer data from
> the new
> table. I wrote a pl/sql procedure to achieve this task, but it takes
> too
> long ( nearly 12 hours) ,
> can you have a look at the below procedure and tell me what is
> wrong
> with it and where i am making mistake? CAn you suggest me a faster
> and more
> efficient way to do that?
> thanks alot for your help in advance.
> cheers :)
> my Pl/sql procedure :
> ********************
> declare
> cursor c1 is
> select c.totid,j.source from join j,cs c where j.source is not
> null
> and trim(j.totid)=trim(c.totid);
> --join is the new table (17700 rows)
> --cs id the older one (50300rows)
> tot cs.TOTID%type;--totid is the primary key for both tables
> src cs.source%type;--source is the column to be updated
> begin
> open c1;
> loop
> fetch c1 into tot,src;
> tot:=trim(tot);
> exit when c1%notfound;
> update cs set source=src where trim(totid)=tot;
> end loop;
> commit;
> close c1;
> end;
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: yilmaz
> INET: yilmaz_at_hwajet.com.tw
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

---

Do You Yahoo!?
Make a great connection at Yahoo! Personals. http://personals.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Oct 31 2001 - 08:33:42 CST