Hello All,
I'm a bit confused with the concept of Fetch size being set on ResultSet and Statement. Based on the information provided
here, I understand that Fetchsize set on Statement or ResultSet will determine the number of round trip calls.
My question is, hypothetically assuming that my table has 1000 rows, and I set my Fetchsize to 50
Now, when I execute my query say SELECT * FROM TABLE (with FETCHSIZE preset to 50), and I call Next() on resultset,
1) Does each call to Next() on ResultSet, results in a round trip call to DB, where the fetched 50 rows are stored in some cache on DB. This does not necessarily mean that SELECT query is reexecuted, but requires a TCP/IP call to DB.
2) Does each call to Next() on ResultSet, results in a round trip call to DB, which involves a TCP/IP call and also a reexecution of the query.
3) or the first invocation returns all the 50 rows, but stored some on some internal cache of the ResultSet, and Next() will simply iterate through that cache. No TCP or DB query execution done.
Also, after 50 rows are returned and processed, and if there is next() call again, would the query be executed again and returns 50 rows and this continues until all the 1000 are returned.
My understanding is, option 1 is correct. i.e, next() call will still require a TCP call to DB server, but instead of executing the query, it fetches the next row from some cache maintained at DB and that is returned. Please correct me, if I'm wrong.
Thanks