Order by performance problem

Hi,

I wrote a sql request with many inner join inside (4). The query takes around 1 to 10 ms to execute. When I add an order by in the request, the time pass to 4 min. The number of rows is not big (around 2000) and the size of the SORT_AREA_SIZE is large enough (10Mo). I used a query on V$SYSSTAT to know if the memory was used for sorting and yes it is. My hard drive is full used during the query (with order by).

I tried an order by on a bigger result but just on one table and it was fast.
The fact that I am using inner join seems to indicate that the sort is not made on the result as I thought but on result part (which can count much more rows).

Could anybody tell me more about what happen and what can I do to sort the result I had without the order by which is very fast?

Thank you,

Fred. Received on Thu Jul 01 2004 - 13:32:49 CDT