Oracle-L: Re: RE: quickest method

From: <rgaffuri_at_cox.net>
Date: Thu, 15 May 2003 10:31:47 -0800
Message-ID: <F001.00599F70.20030515103147@fatcity.com>

i brought this up he other day? It might have bounced. Why not transportalbe tablespace? You move all the tables you want to the tablespace to transport and then transport it.

Im going to do some ETL. and I was thinking of that as an easy method. Am I going in the wrong direction? No one else mentioned it?
>
> From: "Jankovic, Djordje" <Djordje.Jankovic_at_attcanada.com>
> Date: 2003/05/15 Thu PM 01:21:43 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: quickest method
>
> If the source and target are on separate servers sql*loader approach requires spooling data to a file, ftp-ing the file over, running sql*loader. Depending of the size of the data, direct load through database links (insert /*append*/ select ... from xyz_at_link) can be faster than that.
>
> Export/Import can be done through named pipes, even when source and target are on different servers. Anybody tried sql*loader through named pipes: I guess it is doable?
>
> Djordje
>
> -----Original Message-----
> [mailto:MATT.ADAMS_at_appl.ge.com]
> Sent: Thursday, May 15, 2003 8:35 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> the sqlplus copy command is not inheirantly slow.
> I think it has a MAJOR dependance on the speed of
> the disk farm and the speed of the network.
>
> We used it yesterday to move a 52 million row
> table (about 7 gig) in about 52 minutes.
> That's not bad. SQL*Loader may have done it faster,
> but we were satisifed with the speed of the 'copy'
> command.
>
> ----
> Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
> When someone says "I want a programming language in which I
> only need say what I want done", give him a lollipop.
>
> -----Original Message-----
> Sent: Wednesday, May 14, 2003 8:00 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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).
>
>
>

RE: quickest method

If the source and target are on separate servers sql*loader approach requires spooling data to a file, ftp-ing the file over, running sql*loader. Depending of the size of the data, direct load through database links (insert /*append*/ select ... from xyz@link) can be faster than that.

Export/Import can be done through named pipes, even when source and target are on different servers. Anybody tried sql*loader through named pipes: I guess it is doable?

Djordje

-----Original Message-----
From: Adams, Matthew (GECP, MABG, 088130) [mailto:MATT.ADAMS@appl.ge.com]
Sent: Thursday, May 15, 2003 8:35 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: quickest method

the sqlplus copy command is not inheirantly slow.
I think it has a MAJOR dependance on the speed of
the disk farm and the speed of the network.

We used it yesterday to move a 52 million row
table (about 7 gig) in about 52 minutes.
That's not bad. SQL*Loader may have done it faster,
but we were satisifed with the speed of the 'copy'
command.

----
Matt Adams - GE Appliances - matt.adams@appl.ge.com
When someone says "I want a programming language in which I
only need say what I want done", give him a lollipop.

-----Original Message-----
From: Darrell Landrum [mailto:dlandrum@zalecorp.com]
Sent: Wednesday, May 14, 2003 8:00 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: quickest method

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@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@hotmail.com>
Sent by: root@fatcity.com
05/14/2003 02:57 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L
<ORACLE-L@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@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@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@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@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@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@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 Thu May 15 2003 - 13:31:47 CDT