Re: Newbie-Limiting rows returned in query

A copy of this was sent to Paul Bennett <bennett_at_cc.gatech.edu> (if that email address didn't require changing) On Sat, 18 Dec 1999 07:22:42 GMT, you wrote:

>How does oracle impliment this query? Does oracle complete the entire query,
>then return the first 10 rows, or does it stop scanning the
>table/index/whatever after the 10s row? Does ALL_ROWS, FIRST_ROWS effect this
>at all? I would guess know becuase that is just used by the optimizer, but
>then again, stopping after 10 rows in a optimize type thinggy.
>

It depends. If TABLE below is a simple table (not a complex view) then it will read the first 10 rows and stop (COUNT-STOPKEY is the optimizer execution path).

If, on the other hand, TABLE was a view like:

create view TABLE as select deptno, sum(salary) tot_sal from emp group by deptno;

Then we would have to generate much -- probably all -- of the result set and then return the first 10 rows.

If we can return the results on the fly without building the entire result set -- where rownum < x will just return the first N rows.

If we have to build the entire result set (sort on a non-indexed field, aggregates, other operations) then the entire set is built and the first N rows of that are returned.

>-- Paul
>
>peter wrote:
>
>> Yes , try use rownum keyword.
>>
>> select * from table where rownum < 10;
>>
>> John Shaft wrote in message ...
>> >Does Oracke offer some way to liit the rows returned in a query? For
>> >instance say I want so select all the rows in a table sorted by name, but
>> >I only want the first ten names. Can I limit to rows 1-10?
>> >
>> >Thanks!
>> >
>> >shaft_at_meanmutha.com
>> >http://www.meanmutha.com
>> >

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Dec 18 1999 - 08:28:25 CST