14

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?

Bhushan
  • 18,329
  • 31
  • 104
  • 137
Blue Label
  • 427
  • 1
  • 7
  • 15
  • That's an interesting question. Intuitively, I would say it's stored in the memory allocated to the JVM. – Dave Nov 22 '11 at 21:34
  • Somebody please add JDBC as a tag for the question, there will be more views and eventually better answers. – Bhushan Nov 22 '11 at 21:52

4 Answers4

12

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.

Bhushan
  • 18,329
  • 31
  • 104
  • 137
4

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
-1

I got it from Oracle JDBC documentation:

Fetch size

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value. Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object. Fetch size is also used in a result set. When the statement object run a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it.

https://docs.oracle.com/cd/E11882_01/java.112/e16548.pdf

p. 17-4

juzraai
  • 5,693
  • 8
  • 33
  • 47
Chukky Katz
  • 93
  • 6
-3

After you execute the query, the data is returned to the JVM. The JVM handles all the data I/O from that point.

awm
  • 2,723
  • 2
  • 18
  • 26
  • If I run select statement with 1000000 rows after I execute it my jvm heap size didn't change. – Blue Label Nov 22 '11 at 21:35
  • 2
    @BlueLabel: You shouldn't select 1.000.000 rows at once. There MUST be a better solution to your problem. – Martijn Courteaux Nov 22 '11 at 21:37
  • 2
    Heap is allocated ahead of time, it shouldn't increase. Also like Martijn said, if you are pulling in a 1,000,000 rows then you're doing something wrong. – awm Nov 22 '11 at 21:39
  • I just give it as example. Because after I execute the query and didn't start to go over the result set in this stage is it still sorted in oracle or in my jvm – Blue Label Nov 22 '11 at 21:40
  • I am wondering how can someone surely be wrong if he/she is pulling 1M+ records? – Bhushan Nov 22 '11 at 23:21