Perhaps it is late to answer, but you might want to have a table that is populated with PK and column values, via column level trigger indicating the changed column and data?
Aleem -----Original Message----- Sent: Wednesday, May 28, 2003 10:21 PM To: Multiple recipients of list ORACLE-L Subject: Re: Which method is more efficient 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: Abdul Aleem 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).
