ResultSet.next() is too slow for an oracle ref cursor

Question

I call an oracle procedure from java and it returns a ref cursor as result. I cast the ref cursor to a ResultSet and the iteration starts over it.

String query = "{call ...(...)}";
CallableStatement stmt = conn.prepareCall(query,ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(10000); 
.
.
.
stmt.registerOutParameter(x, OracleTypes.CURSOR);
stmt.execute();
Resultset rs = (ResultSet) stmt.getObject(x);

while (rs.next()) {    /** Problem occurs here **/
    ...
}

The problem is that SOMETIMES (not always) for some specific records the ResultSet.next() method takes too long (like 100 secs). Necessary to mention that the number of returned records is at most 25 and the same query in the database behaves executed normally (executes in about 6 seconds).

As I investigated more, I found out that there is a column in my returned cursor which if removed this problem doesn't occur. That column is actually the ROWNUM() which is included in the result cursor.

--ORACLE Query snippet:
OPEN result_cursor FOR 
SELECT "FirstName","LastName", r 
  FROM (SELECT ROWNUM r, *
         ... -- query details
         WHERE ROWNUM <= 25)

I don't even touch that field in the ResultSet but it still causes this problem (which seems unfair :( ). I tried to convert it to string in the Oracle procedure (by concatenating it with a ' '), assuming that a type conversion might cause this issue, but it made no difference in the situation. why is this happening?

Answer 1

The field you are talking about, the ROWNUM(), is a changeable value, since rows may be inserted which might actually change the absolute row number - which then needs to be recalculated when the fetch operation performed, to account for any inserts or deletes from the tables returned by the cursor since the last fetch call. Calling rs.next() causes a fetch operation, each time, because this column is a changeable value, even though you're getting the rest of the results quite quickly.

This happens because using a ref cursor KEEPS THE SELECT OPEN while you are navigating the ResultSet.