ok so your goal is not speed. its reducing redo, correct? so you dont care if its slow?
try using a global temp table. that may cut down your redo significantly. > > From: "Rodrigues, Bryan" <[EMAIL PROTECTED]> > Date: 2003/05/29 Thu PM 12:44:52 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: RE: Which method is more efficient > > Yes, I am in archive log mode and I have had 2 occurances of filling up the > archive log filesystem, but I don't want to change one problem for another. > > Bryan > > -----Original Message----- > Sent: Wednesday, May 28, 2003 3:20 PM > To: Multiple recipients of list ORACLE-L > > > have you run it? isnt that alot slower? you have alot of context switches > also. for every record to update, you then switch to SQL. > > what kind of efficiency improvement are you going for? Speed or cutting down > on redo? Are you in archivelog mode and dont want to blow up your archives? > > > > From: "Rodrigues, Bryan" <[EMAIL PROTECTED]> > > Date: 2003/05/28 Wed PM 02:40:25 EDT > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: RE: RE: Which method is more efficient > > > > The fields that are changed are determined by > > 1) A loop would start until all records in parts change table are done > > 2) Select a part record from the part changes table > > 3) Select the same part from the existing part table > > 4) Compare the value in the parts changes table against the corresponding > > field in the part table 5) After comparing all fields in the records, > create > > record in a seperate work table with the values populated with null if the > > field values matched and the new value if the values did not. > > 6) This loop would continue until all parts are done. > > 7) After any records in the work table where all fields (outside of part > > number) are null are deleted. > > > > This process normally will decrease the number of records to be processed > > after this point by 75%. > > > > Hope that helps, > > > > Bryan > > > > > > -----Original Message----- > > Sent: Wednesday, May 28, 2003 1:21 PM > > To: Multiple recipients of list ORACLE-L > > > > > > oh i missed part of it. the question is how do you figure out which fields > > have changed? if you have to do an anti-join on each field, then do an > > update of every field. > > > > the question is how will you determine which fields have changed? > > > > > > From: DENNIS WILLIAMS <[EMAIL PROTECTED]> > > > Date: 2003/05/28 Wed PM 12:59:51 EDT > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > > Subject: RE: Which method is more efficient > > > > > > Bryan - If this is a critical issue, I would try it both ways on a test > > > database and use log miner to examine the amount of redo that is > > generated. > > > My recollection is that you will find that the redo record records the > > > before and after data for each field. So just updating all fields may > > > generate significantly more redo. But don't trust my recollection on > this > > > issue, test it yourself. > > > > > > Dennis Williams > > > DBA, 80%OCP, 100% DBA > > > Lifetouch, Inc. > > > [EMAIL PROTECTED] > > > > > > > > > -----Original Message----- > > > Sent: Wednesday, May 28, 2003 10:50 AM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Hello everyone, > > > > > > I have a question for the group of which method is more efficient. > > > > > > To set the stage my company has a process to load part changes from > > vendors > > > into the tables in an 8.1.7.4 Oracle database with archiving on and this > > > database has a standby database at disaster recovery site, so nologging > is > > > not an option. > > > > > > There is a discussion going on as to which method is more effective for > > > updating the information in a table. In looking at effectiveness, I am > > > looking at reducing the amount of redo information produced and having > the > > > database do the least amount of work. > > > > > > 1) Method 1 is to update the information only for the fields that have > > > changed, 1 field at a time. > > > 2) Method 2 is to update the information for all the fields in the > > > record whether they have changed or not, 1 record at a time. > > > > > > The size of the record is 1843 bytes and the distribution of field > sizes: > > > 2 fields varchar2(240). > > > 1 field varchar2(150) > > > 15 fields varchar2(50) > > > 1 field varchar2(3) > > > 2 fields varchar2(20) > > > 4 fields varchar2(40) > > > 3 fields varchar2(1) > > > 2 fields varchar2(25) > > > 2 fields number(10,2) > > > 1 field number(13,2) > > > 1 field number(1) > > > 1 field number > > > 1 field varchar2(6) > > > 1 field number (17,2) > > > 1 field varchar2(4) > > > 3 fields that are date. > > > > > > In the past couple of months the average number of fields changed per > > record > > > was 3 to 4 fields per record. > > > > > > Thanks for your help, > > > > > > Bryan Rodrigues > > > Oracle DBA > > > Elcom, Inc. > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Rodrigues, Bryan > > > INET: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS > > > INET: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: Rodrigues, Bryan > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Rodrigues, Bryan > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
