RE: what is the fastest way to update a big table with more than

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 31 Oct 2001 05:15:53 -0800
Message-ID: <F001.003B914C.20011031050020@fatcity.com>

yilmaz,

First of all, your query is probably performing a full table scan on each table because of your use of the TRIM function. Remove this.

Secondly, why join the two tables together? Just query the table with the newer data in a loop, and update the older table like this:

      declare
      cursor c1 is
                 select j.totid, j.source from join j where j.source is not
null;
              tot join.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;
      exit when c1%notfound;
      update cs set source=src where totid=tot;
      end loop;
      commit;
      close c1;
      end;

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, October 31, 2001 7:15 AM To: Multiple recipients of list ORACLE-L rows and 20 columns

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------