Re: Limitting number of rows returned from a select.

On Sat, 22 Jun 1996 07:28:13 GMT, davidp3_at_soho.ios.com (David P) wrote:

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

Yes but done on purpose. My question was "what is the sum of the salaries for the first three depts". My question was somewhat non-sensical, but rather an example.

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

unavoidable given the question. read on.

>Dave

I was trying to show a new twist on an old trick.

Many times it is asked, can I use rownum to limit the number of rows. The answer is yes with lots of caveats. This removes one of the caveats. Lets say you needed the first three sums of sals by dept from the emp table. You could code:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;

	int		d[2];
	float		s[2];
EXEC SQL END DECLARE SECTION;
	int		i;


EXEC SQL DECLARE C1 CURSOR FOR
	select deptno, sum(sal) from emp group by deptno;

EXEC SQL DECLARE C2 CURSOR FOR
	select * from
		( select deptno, sum(sal) 
  		    from emp
		   group by deptno )
	where rownum < 3;


	EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

	EXEC SQL OPEN C1;
	EXEC SQL FETCH C1 into :d, :s;
	for( i = 0; i < sqlca.sqlerrd[2]; i++ )
		printf( "%d, %f\n", d[i], s[i] );


	EXEC SQL OPEN C2;
	EXEC SQL FETCH C2 into :d, :s;
	for( i = 0; i < sqlca.sqlerrd[2]; i++ )
		printf( "%d, %f\n", d[i], s[i] );

Both queries do the same exact thing. In fact if you trace them, you will find they do the same exact amount of work. In pro*c perhaps this trick is not so useful but in a 4gl that doesn't support array fetching or good procedural processing, this is a neat trick that does what you need.

What I was trying to point out was that

select deptno, sum(sal) from emp where rownum < 3 group by deptno

will not work whereas the other query will (and it does just as much, no more, no less then the select deptno, sum(sal) from emp group by deptno query).

Where might this be useful? If you are building an ad-hoc tool and wan't to make sure your users don't kill your network.... Then all you need to do is wrap all of your queries in another query, for example, take your

select blah blah blah from t, t, t, t where some stuff

And submit it as

select * from (
select blah blah blah from t, t, t, t where some stuff ) where rownum < 500;

Then you gaurantee that only 500 rows will ever come back, even though the full result set MAY have been generated on the server (use profiles to limit resources on the server coupled with rownum to limit rows on the network)

Yes, the group by on a large table would take a while BUT a group by and fetching the first N rows would take just as long.

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Jul 01 1996 - 00:00:00 CEST