Re: Fastest way to ASCII file or other ideas?

On May 2, 7:37 pm, EscVector <J..._at_webthere.com> wrote:
> On May 2, 4:11 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
>
>
> > On May 2, 6:52 am, EscVector <J..._at_webthere.com> wrote:
>
> > > On Apr 30, 4:07 pm, EscVector <J..._at_webthere.com> wrote:
>
> > > > I'm looking for suggestions on the fastest way to dump 100+ Million
> > > > rows from Oracle to an ASCII file.
> > > > We've looked at external table with data pump. This works with speed,
> > > > but that format is proprietary. We've also looked at UTL_FILE and
> > > > this is slow. Looking for ideas suggestions. Streams possibly? Any
> > > > third party freeware?
>
> > > Thanks for the replies:
> > > We tested with sqlplus option, but the lack or real programmatic
> > > within sqlplus makes this a last resort option.
>
> > What exactly do you need to do? This started off as simply dumping
> > rows to ASCII.
>
> > > Performance Based Options Synopsis:
> > > 1. Use ProC
> > > 2. Use Perl
>
> > > No need to re-invent the wheel, use existing code samples and come up
> > > with something that works fast.
>
> > > Thanks!
>
> > jg
> > --
> > @home.com is bogus.
> > If an optimist looks a glass of water and says "it's half full", and a
> > pessimist looks and says "its half empty"... The DBA will look and say
> > "that glass looks twice as big as it needs to be - is the water
> > increasing or decreasing? Better get another glass to back it up."
>
> I didn't want the simplest way to dump rows I wanted the fastest
> way.
> Asking for brainstorm, suggestions, ideas, sanity check.
>
> I think simple would be running from sqlplus. That was mentioned.
> I'm biased toward perl or C. These were also suggested

Sometimes the simplest way is the fastest way. I bet many people would be interested in what you find in speed difference between sqlplus and pro-c and perl. I suspect there won't be much, but haven't tested in this century. Since, one way or another, it has to go through the SQL engine anyways (the exception being direct-path on some utilities, which you have ruled out). I suspect there might be a platform-specific difference too, but I don't see that you've specified a platform. Still not sure what sqlplus is missing programmatically - anything you do besides just spitting the data is going to slow you down. Are you ordering, filtering, or what?

jg

--
@home.com is bogus.
http://www.channel4.com/history/microsites/S/sh01//hackers1.html