If I create a scrollable result set like this....
Statement st = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

ResultSet rs = statement.executeQuery("Select * from products");

Let's say that there are 100,000 rows that match the query criteria. My quesiton is: Will the driver perform a physical read to retrieve all 100,000 rows? I would think that this is very inefficient. Can I use setFetchSize(100) to retrieve the first 100 rows? But you can only call setFetchSize() after the result set is returned from the statement. Please help!

sf

Scrollable resultsets are typically implemented with a database curser. The curser allows you to step through subsets of a table, which means that records will be retrieved gradually as you step through your ResultSet.

Most JDBC drivers use these kinds of optimizations for non-scrollable ResultSets as well.

Try Reference Cursor if you are working with Oracle. I do not remember if it is part of SQL92 or any standard.

Ravi

Why do you think you can set fetch size after the RS has returned?

All databases have a default buffer size or tuple size that is sent. Unless you specify stmt.setFetchSize(), the driver will fetch the default tuple size (for example, in Informix, the default tuple size is 32 KB, if I remember correctly, so I presume it will fetch rows worth that much data). If the driver tries to fetch all the data up front, that would be terribly inefficient from many angles. You can rest assured that no sane driver vendor will do this.

I thought that you can only call setFetchSize() after the result set has returned because the method is on the ResultSet interface. You need a result set first before you call the method, don't you?

Just because you have a ResultSet object does not mean that object is populated with all of the database data for the query. Modern JDBC drives will "lazy-load" data in chunks from the database as you step through the ResultSet. The setFetchSize() method just gives you more control over the chunk size.

java.sql.Statement has a "public void setFetchSize(int rows)" method. I am looking at the javadocs for JDK 1.4

why do you think that it works that way? In my understanding the query is just executed when you get rs handler and records are actually fetched when you do rs.next(). So if in between you change the rs properties and assuming that the driver support your changes, the adequate records can be fetched. If your driver does not support then there are other ways to acheive this by query manupulation.

ResultSet is a database cursor, The result of the query will be stored in database as some kind of temp tables and the driver returns the pointer from the first record. When you iterate the result set, it will fetch one record at a time.

If you want to get 100 records first time and 100-200 next time, you can use database dependent attributes in your query such as rowid in oracle to do that.

Thanks,
Senthil.

There is no db-independant way around this, unless you run the entire query each time and grab only the chunk of rows each time by maintaining an offset. I wish there was an attempt at some standardization in this area in the JDBC specs.

Does Oracle maintain the rowId across fragments? I've never used Oracle.