DSXchange

The target is a flat file which is an input file to another datastage job for some transformations.

thanks

More questions:

The target is a flat file, which is processed in the same Datastage server/environment - correct ?

In the transformation stage mentioned, how many more sources is there as input ?

Reason for the questions: It may be possible to solve it within a SP ?

Oh, another question: Is the source table a partioned table (yes I would assume so) ?

And the target after the transformation is Oracle, inside the same server as the sourcetable ?

Folks, the problem here is simple. There is a single Oracle process spooling data once a query has resolved and produces output. There is a finite rate at which a single process can spool data out of Oracle. Once you've achieved that top rate, NOTHING will speed it up.

The ONLY SOLUTION is to have multiple processes spooling data. This fundamental concept is being missed here. The parallel product works on this concept, that you have to use multiple processes to move data.

My earlier recommendation is to segment (partition) your query into logical groups (ranges, mod, etc) and use multiple job instances to each grab a subset of the source data and spool out to a file.

Stored procedures, parallel queries, hints, indexes, NOTHING will help once you have reached the point of a spooling bottleneck.

an case to do it outside of datastage as a sql script spooling to a file.

That way you can put a parallel hint
/*+ full(table_name) parallel(table_name, */
and force it to go in parallel and the dump to file is not slowed down by the server throughput, or if you have PX do it in PX.

The question is more likly if this is the optimum complete solution - why just look at the <select to flat file> option, when it may very well be more alternatives to the problem if one looks outside the box.

So why I ask is: If the target is within the same Oracle installtion, there might not be a need for a flat file.