Where Result set is stored while working with jdbc and oracle driver

Question

Once I use jdbc with oracle driver and run select query is the result of the query is stored in the server of oracle memory or file system or temp table ?

and once I run the next method by getting the next row is it loaded from the oracle server memory to the jvm memory ?

And in case I define the the number of fetch size on the result set to be 1000 is this mean that the 1000 rows are loaded from the oracle to the JDBC driver on the JVM?

Answer 1

A default number of rows (not the entire result set) will be fetched in your local memory. Once you reach at the last line of the fetched rows (say by doing next() and try to access next row) and if there are more rows in the result, then another round-trip call will be made to the database to fetch next batch of rows.

EDIT 1:

You can see how many rows your resultset is fetching at a time by doing this (please verify the syntax):

rs.beforeFirst(); // will put cursor before the first row
rs.last(); // will put cursor after the last line
int noOfRows = rs.getRow(); // will give you the current row number

EDIT 2:

If you want to get more rows in the local memory than usual, you may consider CachedRowSet. Even this will make round-trips, but generally less than normal resultset. However, you should consider doing some performance checks for your applications.

Answer 2

Depending on the exact implementation, part of the resultset will be prefetched into the JVM and part of it will either be in the memory of the Oracle server, or will simply be loaded from the database when more rows are requested.

When executing a query the database does not always need to read all rows from before returning data to the client (depending on the access path of the query, ability of the optimizer, functionality of the db etc).

When you set the fetchSize() on the Statement, you are only giving a hint to the JDBC driver how much you think it should prefetch. The JDBC driver is free to ignore you. I do not know what the Oracle driver does with the fetchSize(). Most notorious AFAIK is (or maybe was) the MySQL JDBC driver which will always fetch all rows unless you set the fetchSize() to Integer.MIN_VALUE.