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.

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