Re: Limitting number of rows returned from a select.

Thomas J. Kyte wrote:
>
> Actually, inline views and group bys can be pretty useful with rownum. Consider
> the following:
>
> select *
> from ( select deptno, sum(sal) sal
> from emp
> group by deptno )
> where rownum < 3
> /

This does work, but we have been using ROWNUM to speed up our queries. If you use ROWNUM, many queries run much faster than querying the whole table and only manipulating the first few rows. Isn't this actually retrieving ALL of the rows in the table, sorting them, and the applying ROWNUM? Not a big deal for small tables, but can bite you if tables get quite a few rows.

Our application is also a bit unusual in that the cursors that usually do this type of query are constantly being opened and closed, which causes the query to be re-executed. If we could keep the cursor open longer, we could just process all of the rows in one loop and only execute the sort once. We're working on that one.

Brian

-- 
Brian M. Biggs                             mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.                       voice: (513) 677-7661
http://www.cincom.com/