asked on

Fetching rows from Oracle in a given range

I have one table which has got around 10 million records and
since this table is so big it takes a while to populate these records
in Crystal
reports. Instead i was thinking of a way to populate say first 1000
records and
then the next 1000 records and so on. So is there any way to populate
records
within a given range, say populating first 1000 records and then
populating
records from 1001-2000,2001-3000
and so on. I thought Rownum might help but it doesn't.
Can somebody help.
Regards,
Subin

ASKER CERTIFIED SOLUTION

dslavin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vlad_impala

As I don't know exactly what you are trying to do and I dont use crystal reports (yet) here are couple of approaches that may give you some ideas.

You could try using PL/SQL procedures that finds the primary key value that corresponds with a given record number in the result set and restrict the SQL query by these values. e.g.
SELECT col1, col2, col3
FROM tab1
WHERE col1 < row_value(2000)
AND col1 >= row_value(1000);

Or you could write a function that takes upper and lower range limits of the record span you want and a given value, and then returns Y or N if the the given value is within the limits. This could be tied back into an SQL query e.g.
SELECT col1, col2, col3
FROM tab1
WHERE in_range(2000,3000,col1) = 'Y';
(i.e. you ask asking if the value of col1 is in the set of rows from 2000 to 3000)

Both approaches would need to order by some known criteria (and indexed column or rowid for example) to ensure consistancy.

Hope this helps
Vlad.

subingeorge

ASKER

Thanks, You have given an excellent solution. I am going to use your second method, using Rowid. that should work.
Regards,
Subin