RE: RE: quickest method

Ryan

   We didn't budget for a tool, especially since this is a "one of a kind" project. I had planned to do it the "old fashioned" way. I plan to run a few tests to see the comparative speed between the different methods. Use Pareto's Rule (80/20 principle), the easiest method for the vast majority of tables, then the fastest way for the few really large tables.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, May 15, 2003 9:27 PM
To: Multiple recipients of list ORACLE-L

so are you going to use ProC or perl to do the extraction? Or are you going to use a tool. Informatica and Ab Initio seem to be popular these days. Ive been told that Ab Initio has a bit of a learning curve. ----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, May 15, 2003 8:11 PM

> I have been following this thread with extreme interest because I have to
> move a large production database between platforms later this year.
> Yes, I saw your earlier post, but too tied up to reply. You are correct
> that transportable tablespaces may be faster than the other methods.
> However, there are a number of limitations that render transportable
> tablespace unsuitable for all situations. In my case, I'm moving to a new
> platform that is not binary compatible. So TTS is out. Bummer.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Thursday, May 15, 2003 1:32 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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).
> >
> >
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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).