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: bulk load of data

Re: bulk load of data

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Sat, 01 Mar 2003 05:58:49 -0800
Message-ID: <F001.0055DC2A.20030301055849@fatcity.com>


John,

Just off the top of my head, I would say that a parallel append INSERT aggregating data from an external table would be the best bet for the summary totals, replacing the "C" code. For the detail, a concurrent parallel append INSERT selecting data (no aggregation) from the same external table(s) would be easy. As with any PX-heavy solution, the bottleneck here would read I/O on the flat-files underlying the external table and write I/O on the resulting tables, assuming you've a fairly capable server. Still, 500,000 rows is not a large volume, so it should load fairly easily...

Even in 8i, single or parallel direct-path SQL*Loader of the detail first and then subsequent parallel append INSERT/SELECT for the aggregation should far outperform a "C" external procedure, unless you have an inadequate server or slow I/O subsystem, in which case the less-demanding tortoise of the single-threaded external procedure may win over the more-demanding hare of multi-threaded direct-path activity...

Just my $0.02...

-Tim

> Age old question I guess, but looking for the up to date answer.
>
>
> We have requirement to process up to 500,000 records of data currently
held
> in
> a text file.
>
> Currently we process these through external C procedures and only totals
are
>
> loaded into the database, Now we have a functional requirement which
> probably
> means we need to load all the records into a database table.
>
> Is sqlloader still the quickest option?
>
> What other options are there? We know that utl_file is too slow. External
> tables?
>
> Platform is 8.1.7 on AIX moving soon to 9i release 2
>
> If there any up to date documents or discussion threads you can point me
at
> please do so.
>
> John
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: John Dunn
> INET: john.dunn_at_sefas.co.uk
>
> 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 Sat Mar 01 2003 - 07:58:49 CST

Original text of this message

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