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

Home -> Community -> Usenet -> c.d.o.server -> Re: Speeding ORDER BY output

Re: Speeding ORDER BY output

From: Jill <jc_va_at_hotmail.com>
Date: Sat, 21 Aug 1999 19:25:22 -0700
Message-ID: <7pnche$1pq$1@bgtnsc01.worldnet.att.net>


Have you done an EXPLAIN PLAN? Could it be that without the ORDER BY it is using an index scan, but not using it with the ORDER BY? You could proof this out, and then add a hint to the code. I doubt you need to mess with the SGA.

Franz Mueller wrote in message <37bd1de5.10084170_at_news.salink.net>...
>Hi,
>
>I have got the following prob:
>
>In an app the user can define the filters he wants to select rows from
>a database. As the filter can be very non-restrictive
>(WHERE NAME Like 'A%', which returns 20000 rows), we try to reduce the
>traffic and improve the performance in the way that we set
>MAXRECORDS-property in our programming environment. This has the
>effect that our programm cuts the result set after let's say 1000
>rows. The problem is that our SQL looks like this:
>SELECT .. FROM .. WHERE NAME Like '...' ORDER BY NAME
>Since the 20000 rows have to be sorted be fore they can be cutted to
>1000 by the app I tried to use the /*+ FIRST_ROWS */ hint to speed the
>out put of the 1000 first rows. The problem is that this does not work
>well (of course there is an index on NAME). The funny thing is that if
>I remove the ORDER BY clause, the output is very fast AND ordered by
>the right column (since ORACLE uses the NAME index to access the
>table).
>Does anybody know why /*+ FIRST_ROWS */ does not work properly in this
>case ?
>
>Has anyone other ideas how to handle the prob a users that try to get
>a huge answer set? (ordered!) and then complain about the poor
>performance?
>
>Thanks
>Franz
Received on Sat Aug 21 1999 - 21:25:22 CDT

Original text of this message

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