Fastest way to select subset of a resultset?
361137Dec 16 2011 — edited Dec 16 2011I have a query which I use to load a result set in Java 1,000 records each time., it looks like this:
select DATA_CLOB as msg from
(select DATA_CLOB, rownum as rn from msg_data where batch_id = ? and category = ? order by msg_data_id)
where rn between ? and ?
When the number of records for a given batch_id,category are small, say a few tens of thousands, its never been a problem
But just recently it had to deal with one batch where there was 1.5 M records and this query is killing us, it's been 3 DAYS now and its not done yet!
There are proper indexes on the columns selected from, and confirmed with Explain Plan that the plan is only doing indexed searches.
Was the way we do the between 2 rownum indexes poor design?
Is there a more efficient way?
Other way I was thinking was maybe just do where rownum < 1000 each time instead of maintaining an index range which is incremented by a thousand each time.
In either case I have both that select and an update which updates a timestamp on the rows that were processed, so I could use that in the where rownum < 1000 query to filter out rows already processed.
Can you think of any other ways to do this quicker?
Again, the sole purpose is to basically iterate through a subset of records from table, 1,000 at a time.