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 DBA