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

"Erik" <no_at_spam.com> 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
>

That might not work..
The Order By happens after the select is complete ( your syntax is incorrect, needs to be: SELECT a FROM t WHERE rownum < 10 ORDER BY b ) ,
 so you would be ordering the 9 records; they might not be the TOP or FIRST records( and probably will not be what is needed). Received on Thu Jan 08 2004 - 11:16:57 CST