Oracle-L: RE: fastest way to update a table with the values from another ta

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Fri, 09 May 2003 11:58:44 -0800
Message-ID: <F001.00595B59.20030509115844@fatcity.com>

Why are you even doing a join? Which table is col5 in?

Make sure there is an index on col5. since the primary key values are in both tables, you do not need to join the tables together.

change any one of the loops do not join the tables together:

procedure proc is

cursor cur_update is
SELECT primary_key, col1, col2, etc...
FROM tab2
WHERE col5 in ('A','B','C');

BEGIN FOR rec_update IN cur_update
LOOP
UPDATE tab1

       SET (col1,col2,etc...) = rec_update.col1,etc...
         where tab1.primary_key = rec_update.primary_key;

END LOOP;
END; hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, May 09, 2003 2:43 PM
To: Multiple recipients of list ORACLE-L table

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

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

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

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