fastest way to update a table with the values from another table

From: <rgaffuri_at_cox.net>
Date: Fri, 09 May 2003 10:43:23 -0800
Message-ID: <F001.00595955.20030509104323@fatcity.com>

Dont want to milk the group too much... on a new a project and doing things I havent done before. This one has bothered me for a week. I tried methods on Jonathan Lewis site and from Guy Harrison's book(which is also on Metalink) and are both slower. Anyway to speed this up? Cant do a bulk bind since we dont have the memory to store all the data.

Both tables have about 2.5 million rows. As of right now we do not have the parallel query option, but do have the partitioning option.

We are in version 8.1.7.3 on a Solaris system.

We have an update statmenet of the form:

UPDATE tab1
  SET (col1, col2, etc...) =

      (select col1, col2, etc...
          from tab1 t1,tab2 t2
         where tab1.primary_key = tab2.primary_key)
WHERE EXISTS (select col1, col2, etc...
          from tab1 t1,tab2 t2
         where col5 in ('A','B','C') and
         tab1.primary_key = tab2.primary_key))

We have accurate statistics and the CBO does a Hash Join. Both tables are about the asme size. Our 'in' (A,B,C) trims out less than 1% of the rows.

---

Method 2:

Now the FAQ on Jonathan Lewis's site says to do the following, but this is about 1/3 slower.

UPDATE tab1
  SET (col1, col2, etc...) =

      (select col1, col2, etc...
          from tab1 t1,tab2 t2
         where tab1.primary_key = tab2.primary_key)
WHERE  (col1, col2, etc...) IN 
        SELECT (col1,col2,etc...
          FROM tab2 t2
         WHERE col5 in ('A','B','C');

---

Method 3:
Guy Harrison's book and Metalink say to use PL/SQL, but this takes twice as long. Im guessing because of the context switches. This one takes twice as long.

procedure proc is

cursor cur_update is
 SELECT t1.rowid rowid, t2.col1, t2.col2, etc...    FROM tab1 t1,tab2 t2
  WHERE col5 in ('A','B','C') and

        tab1.primary_key = tab2.primary_key;

BEGIN FOR rec_update IN cur_update
  LOOP
    UPDATE tab1

       SET (col1,col2,etc...) = rec_update.col1,etc...     where rowid = rec_update.rowid;
  END LOOP;
END;            
--

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

Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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 Fri May 09 2003 - 13:43:23 CDT