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).