Re: limiting result set size

> > I'm assuming this isn't an uncommon problem, so there are probably good
> > solutions.

Indeed we have the same problem.

I took a shortcut and decided that it was ok in our application to lose records if exceptional conditions occurred. That let me do basically what Jonathan Lewis described except without the added complexity of actually locking records and deleting them.

Instead we keep a table that has the last id "locked" by one of the worker clients. We update and retrieve the range to work on in a single atomic sql transaction (using "returning") and process those records.

We do 1000 records at a time and have 10-20 worker threads running concurrently. I don't think we've hit the point of diminishing returns yet, though eventually I fear having sort_area_size * #threads become too large.

Does oracle have any better solution for this? I've heard of a builtin queuing feature for example, does it solve this problem more directly?

-- 
greg