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 <DWILLIAMS_at_LIFETOUCH.COM>
> Date: 2003/05/28 Wed PM 12:59:51 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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.
> dwilliams_at_lifetouch.com
>
>
> -----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: BRodrigues_at_elcom.com
>
> 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: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> 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 Wed May 28 2003 - 12:20:33 CDT
