Re: instead of SELECT TOP: cursors or nested queries?

Erik wrote:

>>>>I'm using Oracle 9i. I need an equivalent of Sql Server's SELECT TOP
>>>>with ORDER BY.
>>>>I have found two different approaches to do this.
>>>>1- Declaring a cursor and fetching the rows I need.
>>>>2- Using a nested query to order the table in the inner select and
>>>>rownum in the outer.
>>>>
>>>>Both solutions work right, but I want to know which has a better
>>>>performance when my tables become large, i.e. more than 10K records.
>>>>I guess that if I create an index on the column I order by there
>>>>should be no significant differences in performance between those
>>>>solutions, but I'm not sure.
>>>>
>>>>Can anybody give some advice?
>>>
>>>
>>>You don't need a nested query:
>>>SELECT a FROM t ORDER BY b WHERE rownum < 10
>>>will work just fine, and probably have the best performance.
>>>An index on b could be used to eliminate the sort, but I don't think
>>>it will make a big difference with just 10k rows
>>
>>What you suggest will not work. The in-line view approach is the correct
>>solution for basic SQL and using RANK the other approach worthy
>>considering depdending upon the application.

> 
> 
> Oops, I was wrong. Now I'll have to change all statements in my entire
> application that use this combination (fortunately not many).
> I guess the reason it has always worked for me is that the column is indexed
> so the rows are retrieved in order from the index.

That will work right up until it doesn't. Which might well mean a few updates or deletes followed by inserts.

I'd look at RANK as it is probably the most direct approach: http://www.psoug.org/reference/rank.html

But alternatively you are always better off explicitly ordering in a select statement and then selecting from that in the form:

SELECT ...
FROM (
    SELECT ...
    FROM ...
    ORDER BY ...
       )
WHERE ...

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)