| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: quickest method
Thanks to everyone for all their suggestions !
Its likely that my source will be an Oracle database (as opposed to a flat file) -- so I'm wondering whether its worth the effort to extract from the source db into a flat file in order to take advantage of Sql*Loader's speed on the loading side. Sounds like it may be because I'll be loading 1+ gigabytes of data. I was thinking it would be quicker to go directly from database to database using database links, but it sounds as though its one of the slower options give the data volume.
Has anybody tried the new "external table" feature within 9i that allows access to a flat file from within Oracle (as though it was a regular oracle table)? Any thoughts on how this new method of accessing and loading flat file data compares to SQL*Loader as a method of loading ?
Carol
>From: Clinton Naude <clintonn_at_meb.co.za>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: quickest method
>Date: Thu, 15 May 2003 00:16:38 -0800
>
>I agree, but SqlLoader can also take ages to do the set up, and by the time
>you have finished the set up and fixed all the error codes etc, the Import
>would have finished.
>If its millions of records, SqlLoader is the way to go, but if it's a
>smallish table, Import - without a doubt.
>
>Or even using a create table as select * from tablename_at_dblink works great
>if it's a smallish table without any constraints.
>
>Just my 2 cents worth.
>
>-----Original Message-----
>Sent: 15 May 2003 02:00
>To: Multiple recipients of list ORACLE-L
>
>Also Carol,
>
>I agree with Jared - my experiences have almost always shown SQL Loader as
>the fastest way to go when the conditions are right. Obviously there are
>lots of variables which determine speed (hardware platform, CPU speed,
>record length, the list goes on) but I have frequently had SQL Loader in
>the 1+ million records per minute speed range with very little work on my
>behalf.
>
>When looking into SQL Loader there are a few things which can have a big
>impact on performance:
>
>1) Direct loading (similar to insert /*+ append */) - this can be very
>fast but has some restrictions on when it can be used
>
>2) Parallel loading (and direct parallel loading)
>
>3) Removing indexes before using SQL Loader - if you are using
>conventional loads it can save a lot of time if indexes are removed from
>the destination table before the load and then recreated.
>
>Regards,
> Mark.
>
>
>
>
>
> Jared.Still_at_radis
>
> ys.com To: Multiple
>recipients
>of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent by: cc:
>
> root_at_fatcity.com Subject: Re: quickest
>method
>
>
>
>
>
> 15/05/2003 09:01
>
> Please respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>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).
>
>
>
>
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> >>>>
> Privileged/Confidential information may be contained in this message.
> If you are not the addressee indicated in this message
> (or responsible for delivery of the message to such person),
> you may not copy or deliver this message to anyone.
>In such case, you should destroy this message and kindly notify the sender
> by reply e-mail or by telephone on (61 3) 9612-6999.
> Please advise immediately if you or your employer does not consent to
> Internet e-mail for messages of this kind.
> Opinions, conclusions and other information in this message
> that do not relate to the official business of
> Transurban City Link Ltd
> shall be understood as neither given nor endorsed by it.
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> >>>>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Mark Richard
> INET: mrichard_at_transurban.com.au
>
>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: Clinton Naude
> INET: clintonn_at_meb.co.za
>
>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: 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).Received on Thu May 15 2003 - 08:46:39 CDT
 |
 |