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

not all of them, nearly 16000 rows have the same totid now i am trying to get rid of that trim(). thanks for any help
cheers :)
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 31, 2001 9:45 PM 50000 rows and 20 columns

> 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).
>

-- 
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).