Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Speeding ORDER BY output
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 Fri Aug 20 1999 - 04:32:16 CDT