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: Tim Gorman <tim_at_sagelogix.com>
Date: Wed, 14 May 2003 18:22:19 -0800
Message-ID: <F001.00599290.20030514182219@fatcity.com>


PRO*C using array fetches. Nothing's faster than a simple "printf()", especially if you increase output buffer settings with "setbuf()"...

on 5/14/03 6:01 PM, Ryan at rgaffuri_at_cox.net wrote:

> If SQLLOADER is the faster to load data, what is the fastest to unload it?
> UTL_FILE is notoriously slow.
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, May 14, 2003 7:01 PM
> 
> 

>> 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: Tim Gorman
  INET: tim_at_sagelogix.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 - 21:22:19 CDT

Original text of this message

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