Re: Need help on limiting # of rows returned...

In article <MLOENNRO.94Feb11102500_at_demo1.se.oracle.com> mloennro_at_se.oracle.com writes:
>In article <1994Feb11.004859.25154_at_mnemosyne.cs.du.edu> balimi_at_nyx.cs.du.edu (bob alimi) writes:
>
> >
> > Is there _any_ way to horizontally partition fetches from a table? I
> > need to retrieve a subset of rows from a table and the rows must
> > be ordered.
> >
>[example deleted]
>
>Strictly speaking: NO. This type of question is not suitable for SQL.
>However, in some situations you can choose to rely on Oracle using a
>certain index to retrieve the rows in the preferred order, and then
>limit the number of rows returned with "where rownum < ..".
>
>You really need 3GL logic to solve this type of problem. The program
>would fetch (an array of) rows and process them one at a time and
>stop when some condition was met. For example, suppose you want to
>fill a truck with order-items from a warehouse. You must select items
>up to a certain predefined total weight. This is much harder than
>your example, but it's still basically the same problem. It can easily
>be done in PL/SQL:
>
>create or replace procedure packItems
>( truck in integer, maxWeight in number ) is
> cursor c is select * from order_items where ... order by ...;
> totWeight number;
> done exception;
> truckNotFilled exception;
>begin
> lock table order_items in exclusive mode; /* simplifies example */
> for item in c loop
> totWeight := totWeight + item.weight;
> if totWeight < maxWeight then
> update order_items set truckId = truck where current of c;
> else
> totWeight := totWeight - item.weight;
> raise done;
> end if;
> end loop;
> raise truckNotFilled;
>exception
> when done then
> null;
> when truckNotFilled then
> ...
>end;
>[There's probably gonna be a couple of syntax errors in this since I
>haven't bothered testing it]
>
>Generalized: A restriction where each rows fulfillment of the query-
>condition is dependant on one or more *other* rows in the same query
>is not suitable for SQL. [Does anybody understand that ?]
>
>hope this helps,
>
>
>
>--
>
>Magnus Lonnroth
>Tech.Sales & Consultant
>Oracle Sweden
>Mail: mloennro_at_se.oracle.com

The original post on this thread specified as a requirement that not all the rows actually be returned (to the client, I infer) due to performance considerations.   

My understanding of cursors (likely incorrect 8^) is that they retrieve ALL of the rows into memory, the developer can then access the rows individually as shown in the example. If so, the solution above will work, but it might not really address the performance issue.

-- 
Peter Sterne, Sr. Software Engineer
Greenwich Associates, Greenwich Connecticut
pete_at_greenwich.com, voice (203)625-5089, fax (203)629-1200