Re: quickest method

Unloading with Perl is *very* fast.

Much faster the spooling with sqlplus.

There's no way I would use UTL_FILE for that.

Perl is not only fast, it's flexible. eg. you don't have to write the files on the database server.

Jared

"Ryan" <rgaffuri_at_cox.net>
Sent by: root_at_fatcity.com
 05/14/2003 06:01 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: quickest method

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