Re: Performance in ORDER BY

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 22 Sep 2005 20:40:01 +0200
Message-ID: <dgutq2$ol2$1@news.BelWue.DE>

Sybrand Bakker wrote:

> 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.

No it doesn't. At least not for 9i and up. (I don't have older versions at hand to test it).
This is the application of a pagination technique often demonstrated by e.g. Tom Kyte. However, it's quite unusual to go beyond say 200 because it's typically used to restrict output to a page oriented client like a browser - and how realistic is it to go to page 800 of a resultlist?

> 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

Regards,

Holger Received on Thu Sep 22 2005 - 13:40:01 CDT