Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Order by performance problem

Re: Order by performance problem

From: Sybrand Bakker <sybrandb_at_hccnet.nl>
Date: Fri, 02 Jul 2004 07:04:30 +0200
Message-ID: <p4r9e0dhll4pv8abu07cofeslu92b6uls5@4ax.com>


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 DBA
Received on Fri Jul 02 2004 - 00:04:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US