Oracle sort in Parallel jobs

rohanf · Post by **rohanf** » Tue May 26, 2009 8:58 am

Hi,

I am on the performance tuning phase of the project. Need some help in how the data sorted in Sequential stage can be used as is without resorting.

My Question is:
I have an Oracle stage with 26 million records and to bring down the time taken to execute the job in datastage I want to push the sorting on the oracle side.

The concern is if the data is sorted in oracle(Sequential stage) and then partitioned in datastage on the same key on which it has been sorted, is the sort order maintained?

Thanks & Regards
Rohan

miwinter · Post by **miwinter** » Tue May 26, 2009 9:47 am

I'm not using v8 right now but does stable sort not still exist as part of partitioning operations within input links? This would allow you to do as you wish

ray.wurlod · Post by **ray.wurlod** » Tue May 26, 2009 4:28 pm

There's nothing to change the sort order when you're partitioning. Every separate node will be correctly sorted. Should you ever need to bring the data back to sequential processing, make sure that you specify Sort/Merge as the collection algorithm, and base it on the sort keys.

rohanf · Post by **rohanf** » Fri May 29, 2009 12:08 am

Hi miwinter,

Does the Stable sort option not consume the scratch disk space if the data is already sorted?

My problem is also the amount of space required for scratch disk in case 26 million recods are put into the scratch disk.

However note that, I have tried what Ray had suggested, sorting on Oracle side and Partitioning on the same key does not shuffle the sort order.

Thanks to all

Regards
Rohan