Slow sorting query: 'order by' problem

I have a question about slow sorting query which fetches around 5,500 rows ordered by non indexed column from the table. It takes around 25 seconds to get and display the result set if 'order by' clause is not used. Otherwise, it takes
1.5 min to display the result set.

SORT_AREA_SIZE is 2MB and hard drive is not used when sorting. I am under impression that sorting in memory makes Oracle 'go to lunch'. I use FIRST_ROWS and
nested loops in the query so ideally it should take 25 sec(fetch the rows that meet search criteria,
display them) + time it takes to sort 5,500 rows in memory. I don't believe sorting
of 5,500 rows takes over 1 minute. Can it be that 'order by' supresses use of FIRST_ROWS
and nested loops(it looks like, because without these two hints select without 'order by' takes same
1.5 min...)? Is there a way around slow sorting?

thanks in advance Received on Tue Sep 05 2000 - 22:59:50 CDT