Re: Restrict number of rows returned?

From: Stephan Born <stephan.born_at_beusen.de>
Date: Tue, 02 Jan 2001 14:18:21 +0100
Message-ID: <3A51D51D.362B0975@beusen.de>

tore wrote:
>
> Is there a way in Oracle (8.0.5) to restrict the number of rows returned
> from a query (using SQL Worksheet or SQL*Plus)?
>
> (Something similar to either SELECT TOP n * or SET ROWCOUNT n in SQL
> Server).
>
> The restriction must be carried out on the server side since I am testing
> some queries that return large result sets over a slow communications linkto
> review the results of a "batch" application.

Yes, try the following:

select *
from large_table
where rownum <= 100;

this will return only 100 rows....restricted by the server (look into the execution plan)

But the enumeration of the rows will be done BEFORE any sorting.... so if you want to sort your data you have to do the following

select *
from

   (

      select *
      from large_table lt
      order by col_to_order

   ) sub
where rownum <= 100;

Ordering in netsted can only be done in Ora8 and up

Hope this will help,

Regards, Stephan

--

Received on Tue Jan 02 2001 - 07:18:21 CST