1. to totally eliminate redo, load your staging records into a global temp table. it
has absolutely no redo and is very fast.
2. if your in 9i, use an external table and a merge command and update the table
directly from the file. dont even load the records to a staging table. Do a search for
this on asktom. He explains it in detail.
3. This is the fastest update statement you can right...
UPDATE(select a.col1 a_col1, b.col1 b_col1
from tab1 a, tab2 b
where a.pk = b.pk)
set a_col1 = b_col1;
need primary keys on both tables or it wont work. This is hands down the fastest
update you can do.
>
> From: "Rodrigues, Bryan" <[EMAIL PROTECTED]>
> Date: 2003/05/28 Wed AM 11:49:49 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Which method is more efficient
>
> 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: <[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).
