Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: quickest method

Re: quickest method

From: Darrell Landrum <dlandrum_at_zalecorp.com>
Date: Wed, 14 May 2003 16:00:16 -0800
Message-ID: <F001.00599052.20030514160016@fatcity.com>


A minor addendum:
Based on this info, I'd say that using the sqlplus copy command is probably the slowest. In one scenario using sqlplus copy to copy some tables, about 5 hours into what turned out to be a 12 hour process, I started exporting the same tables, copied across to the target server, and imported in less than 1/3 the time.
I don't have a lot of experience with SQL Loader, but in a few optimized cases (using direct load), SQL Loader screamed.

>>> Jared.Still_at_radisys.com 05/14/03 06:01PM >>> Carol,

Hands down, SQL Loader is the fastest.

Export/Import is rather slow.

SQL and PL/SQL commands can be on either side of exp/imp, depending on what you are doing and how well the code is written.

e.g. SQL statements are fairly fast, PL/SQL for loops are not. Pl/SQL

bulk
processing is fast.

Unless you need the programatic abilities of PL/SQL, use SQL Loader.

Exp/Imp can still be useful, even with SQL Loader. Use exp/imp to build
your tables, then the indexes and constraints after the data is loader.

No pat answer as to how to load data, depends on your requirements.

There's probably no point in messing with SQL Loader if the data sets are small, and you can easily export from another database and then import.

If the data is in CSV or flat files though, and/or is very large, SQL Loader
is very fast.

HTH Jared

"Carol Legros" <carol_legros_at_hotmail.com> Sent by: root_at_fatcity.com
 05/14/2003 02:57 PM
 Please respond to ORACLE-L  

        To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

        cc: 
        Subject:        quickest method


I'm curious to know whether anyone out there has seen a comparison discussing the pros and cons and/or results of any simulation tests that
compare the speed with which data can be loaded into a target database

from
a source (database or flat file) using the following 3 methods :

(i) Export (from source), Import (to target)
(ii) SQL*Loader (to target)
(iii) SQL or PL/SQL commands (insert to target)

      using a Database Link between source &
      target

I'm working on a data loading strategy and since there are "many ways to
skin a cat", I'm considering these as options. Of course, there are other

criteria that impact the method chosen, but assuming all things are equal
(ie network bandwidth is good, access to both source and target are not
an

issue etc.), which of these methods would be quickest ?

Thanks,
Carol



Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Carol Legros
  INET: carol_legros_at_hotmail.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: INET: Jared.Still_at_radisys.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: Darrell Landrum INET: dlandrum_at_zalecorp.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).
Received on Wed May 14 2003 - 19:00:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US