Re: limiting result set size

We want the same thing. We have 25 machines running the same application aganist one Oracle server. Each machine will be doing this:

1. Begin trans
2. select N (viz. 16) rows from "work" table
3. Apply the work to the affected (other) tables
4. delete the N rows from the work table
5. Commit the transaction

We have two reasons for wanting to do this: 1. The transaction creating the work is real time sensitive, and inserting into

    the work table is quicker than "doing the work" 2. We have a lock contention problem on some rows the "work" applies to

    and by batching N changes to one row like this we get less contention.     For this to be successful, the "select work" query should get as many     changes as it can for the same rows. The work table is indexed on the     column that identifies the rows the work applies against.

We have two problems:
1. Getting just N "work" rows
2. Not blocking the other machines when one machines has the first N "work"

    rows locked.

Any tips are greatly appreciated!

Brian Beuning

Matt Brown wrote:

> This might be a FAQ, but so far my research hasn't turned up an answer.
>
> My situation is this. I plan on having a scalable java application
> which will process rows from a table. 1..n of these applications could
> be deployed to distribute the processing.
>
> Each application will need to execute a query or call a stored procedure
> to get a set of rows to work on. What's the best way to accomplish
> this?
>
> Is there a way to limit the number of rows returned in a result set?
>
> Is it better to have each application invoke a SELECT...FOR UPDATE query
> (itself or via a stored procedure) or have them talk to some other
> applicaiton which is font-ending the database and doling out x number of
> rows from the total result set to each requestor??
>
> I'm assuming this isn't an uncommon problem, so there are probably good
> solutions.
>
> Any help appreciated.
>
> Matt
Received on Sat Apr 01 2000 - 00:00:00 CST