> >>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.
Received on Thu Jan 08 2004 - 14:19:40 CST
