Difference between setting a fetch size on Statement vs ResultSet

Question

What is the difference between setting a fetch size on Statement vs ResultSet? Oracle documentation says that if fetch size is set on a Statement then the same is used by ResultSet as well, as long as the fetch size is set prior to obtaining the result set. What difference does it make if I set it on Statement or a ResultSet? I am using Oracle database. Following is Oracle documentation about it:

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.

Answer 1

The only place the fetch size really matters is on the ResultSet. When a ResultSet is created, it gets its fetch size from the Statement that was used to create it, but it can be changed later.

Think of the Statement's fetch size as a default that will be passed to all the ResultSets it creates. If you don't want to use this default, you can override it.