Limiting Large Result Sets

Hi,

 I would be glad if someone could provide solution for this problem.

working ENV:

   Jdeveloper 10.1.3 - Development
   Oracle 9i - Database
   Windows XP - OS

I want to retrieve about 30 rows at a time . The table contains over 2 million rows. I would like to begin at the first row and drag 30 rows over the network. Then get the next 30 and repeat the process untill all rows are displayed.

I made 2 approaches for this:
1) cache based
2) query based

Using cache based approach, i was running out of memory error because all millions rows cannt be cached. Thats obvious. This kind of approach was working good for 10,000s of rows.

Using query based approach, i was able to retreive all the million rows by limiting the query. I run the query everytime to get the rows. This is working absolutely fine but performance is not really good. And this kind of approach is not acceptable at my work place.

So, i need to do work with resultset itself. I tried to set max rows on statement using:

              prepareStatment.setMaxRows(10);
              resultset = prepareStatment.executeQuery();

Here, i can obtain first 10 rows but if i need to get from 11- 20 and 20-30 rows and so on.......how do i achieve it????

Any other suggestions are also acceptible. Looking forward for the reply.

Thanks. Received on Tue Aug 29 2006 - 17:02:16 CDT