Oracle-L: Re: Which method is more efficient

From: <rgaffuri_at_cox.net>
Date: Wed, 28 May 2003 09:14:57 -0800
Message-ID: <F001.005A4713.20030528091457@fatcity.com>

to totally eliminate redo, load your staging records into a global temp table. it has absolutely no redo and is very fast.
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.
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" <BRodrigues_at_elcom.com>
> Date: 2003/05/28 Wed AM 11:49:49 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: 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 Wed May 28 2003 - 12:14:57 CDT