| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: fastest way to update a table with the values from another ta
took 60% longer. any other ideas? We have our percent free and used set to the defaults. Ill change that on monday and see how much the greater density helps the update statement.
any other suggestions? I also tried an update method I got off of asktom of the form
update (select tab1.col1 tab1_col1, tab2.col1 tab2_col1
from tab1,tab2
where tab1.primary_key = tab2.primary_key)
set tab1_col1 = tab2_col1;
this was about 10% slower. The WHERE EXISTS doesnt feel right. Seems like its doing to selects and two seperate join operations. Shouldn't something else be faster? We are update 99.9% of the second table.
>
> From: "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us>
> Date: 2003/05/09 Fri PM 03:58:45 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: fastest way to update a table with the values from another ta
>
> 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
> ---------------------------------------------------------------------
> 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: <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 - 16:25:47 CDT
 |
 |