Re: Limitting number of rows returned from a select.

On Fri, 21 Jun 1996 09:35:08 -0400, "Brian M. Biggs" <bbiggs_at_cincom.com> wrote:

>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/
Nothing wrong with using ROWNUM
But ... in this select SUBQUERY works first and ohoh, there is GROUP BY clause, what it does?
It scans ALL rows selected with subquery and only after that cook first N rows
So, performance would be quite *cough*, pardon me... Dave Received on Sat Jun 22 1996 - 00:00:00 CEST