I know this is very optimistic question but just want to check i have any luck.

In our client ware house, there is a query running on 115 million rows table doing order by 29 columns. As we have a limited pga_aggregate_target (6G) this sort is going to TEMP table space and is consuming lot of time. The query in this example is run for 9 hours.

The query is like this ...

select
col 1,
col 2, ....
col x
from table order by
col 1 ...
col 29

Just trying to find is there any work around available to get rid of order by on such a big table.

Your suggestions are highly appreciated.

Thanks,