RE: quickest method

For a two terabyte data transfer at one client, we wrote a C extractor, ran it in four-plicate on partitioned data, sent the output directly to pipes, sent those pipes zooming across the 400 Mbit network into waiting pipes that served as the input files for direct path SQL Loader.

That was amazingly fast, and loads of fun, sort of like a data warehouse rube goldberg device.

Actually, it was blazingly fast in test, I left the project before it hit production...

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Mladen
> Gogala
> Sent: Thursday, May 15, 2003 12:27 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: quickest method
>
>
> Essentially, yes, that's precisely the way to do it. You do an array
> fetch
> into a C/C++ array and write it out. As for setbuf, setvbuf
> and printf,
> in
> my opinion, the best thing to do is to use mmap to map the file to a
> buffer
> and then use sprintf to populate the output buffer. If you
> don't want to
> play with mmap/munpap you can always use just a simple and
> unpretentious
> write call, just like printf does.
>
>
>
> On 2003.05.14 23:17 Ryan wrote:
> > I know C/C++ moderately well. I thought PRO*C just added pragmas to
> > embed
> > SQL? Why is it faster? So your just doing some selects into
> variables
> > and
> > Printf() to a file?
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, May 14, 2003 10:22 PM
> >
> >
> > > PRO*C using array fetches. Nothing's faster than a simple
> > "printf()",
> > > especially if you increase output buffer settings with
> "setbuf()"...
> > >
> > >
> > >
> > > on 5/14/03 6:01 PM, Ryan at rgaffuri_at_cox.net wrote:
> > >
> > > > 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: 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).
> >
>
> --
> 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).
>

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.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: Yosi Greenfield
  INET: ygreenfield_at_kewsystems.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).