Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Order by performance problem
On Thu, 1 Jul 2004 20:32:49 +0200, "azerty" <toto_at_toto.fr> wrote:
>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.
>
We would need to see the actual statement and the explain plan. Right now you think you know what the cause is, and consequently you submit a riddle.
-- Sybrand Bakker, Senior Oracle DBAReceived on Fri Jul 02 2004 - 00:04:30 CDT