Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance in ORDER BY
On 22 Sep 2005 08:20:44 -0700, "jabelsc" <jabelsc_at_gmail.com> wrote:
>Hello everybody!!
>
> We have a SELECT running over our database. We only need several
>registers of a table of 800.000 values. In the SELECT when we put in
>comments the ORDER BY T.START_TIME DESC line, the instruction is
>executed in 7 seconds, but if we use the ORDER BY line, the execution
>goes to 8 minutes.
>
> I would appreciate any tip very much, cos I am desperate with it.
>
> Thanks in advance.
>
> Abel S.
>
> SELECT CCID,GROUP_ID, ....(other fields)
> FROM (
> SELECT S.CCID, S.GROUP_ID, ...., ROWNUM AS RN
> FROM SITEINFO S,TIMESTMP T, ADDEF A, UNIT U
> WHERE A.TYPE ='DI' AND T.STATUS = 'C' AND T.USER_ID IS NULL
>AND S.CCID = T.CCID AND S.GROUP_ID = T.GROUP_ID AND T.FUNCT_NUM =
>A.FUNCT_NUM AND U.FUNCT_NUM = T.FUNCT_NUM AND T.VALUE_NO =
>A.VALUE_NO
> ORDER BY T.START_TIME DESC
> )
> WHERE RN BETWEEN 4051 AND 4125;
It would help to post a version and it is usually almost compulsory,
as most answers are version dependent.
First of all your statement is incorrect: there rownum need to be
*outside* the inline view.
Secondly performance of a sort is determined by
- the init.ora parameter sort_area_size (pre 9i) - the init/spfile.ora pga_aggregate_target_size (9i and 10g) - The sizing and location of your temporary tablespace.
-- Sybrand Bakker, Senior Oracle DBAReceived on Thu Sep 22 2005 - 13:17:17 CDT