Re: Performance in ORDER BY

"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;
Oracle isn't quite smart enough to optimize this.

you need something like:

...WHERE RN BETWEEN 4051 AND 4125 and rownum<=75;

Once the rownum limit is in place, you can omit the upper limit on the RN (where RN >=4051 and rownum<=75)

And of course, if there isn't an index which lets it avoid the sort, then this will do little good.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB