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" <BRodrigues_at_elcom.com>
> Date: 2003/05/29 Thu PM 12:44:52 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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" <BRodrigues_at_elcom.com>
> > Date: 2003/05/28 Wed PM 02:40:25 EDT
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > 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 <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).
> > --
> > 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: <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: 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: <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 Thu May 29 2003 - 13:30:15 CDT
