According to the JDBC specifications, the Statement.setMaxRows(int maxRows) method is supposed to:
Sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number. If the limit is exceeded, the excess rows are silently dropped.
When testing it against limiting the result set at the SQL level (e.g. ROWSET), both the JDBC and the SQL construct seem to perform very well.
Even when selecting millions of rows, the setMaxRows doesn't seem to perform worse.
Does the JDBC driver send any hint to the database server to optimize the plan for this specific result set size?
Does the Executor open a server-side cursor so the client can fetch records on demand, so when the maxRows threshold is reached, the database can close the cursor for good?
I suppose the database doesn't select the whole result set, only to send it over the wire so the client can simply discard the overflowing records.