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

i call oracle procedure java , returns ref cursor result. cast ref cursor resultset , iteration starts on 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 (not always) specific records resultset.next() method takes long (like 100 secs). necessary mention number of returned records @ 25 , same query in database behaves executed (executes in 6 seconds).

as investigated more, found out there column in returned cursor if removed problem doesn't occur. column rownum() included in result cursor.

--oracle query snippet: open result_cursor  select "firstname","lastname", r    (select rownum r, *          ... -- query details          rownum <= 25) 

i don't touch field in resultset still causes problem (which seems unfair :( ). tried convert string in oracle procedure (by concatenating ' '), assuming type conversion might cause issue, made no difference in situation. why happening?

the field talking about, rownum(), changeable value, since rows may inserted might change absolute row number - needs recalculated when fetch operation performed, account inserts or deletes tables returned cursor since last fetch call. calling rs.next() causes fetch operation, each time, because column changeable value, though you're getting rest of results quite quickly.

this happens because using ref cursor keeps select open while navigating resultset.