Hi, the first rows hint doesn't tell Oracle to only return the first rows :-)
It just changes it's target for an initial response back to you.
Ie, in a batch processing job of thousands of records, there are no users waiting for a response so you want the job done as quick as possible. The hint would be /*+ ALL_ROWS */ which tells Oracle to aim to complete the whole job as soon as possible.
When a user is waiting for something on screen, they will usually only see the first top <n> rows of a grid. While they're looking at that Oracle can continue to get the rest of the rows in the background. All rows are still returned but the *initial response* is quicker. The FIRST_ROWS hint will still return all rows in the cursor.
Try it, see what happens! Maybe in a Java resultset it won't be any difference if the ResultSet insists on getting all rows anyway, I don't know.
I can toggle performance for users on systems who (stupidly) query an archive table returning 20,000 rows from 1 minute to 1 second using this Oracle hint combined with my client side dataset property of
DataSet.QueryAllRecords := True
DataSet.QueryAllRecords := False
Ah, now I see why DESC is no good for you. Also you would need /*+ ALL_ROWS */ as you need the last row / entire result set as quick as possible.
Unfortunately I would think there is little you can do to improve the performance using the method you have for paging, some ideas....
The narrower the resultset (ie fewer columns/bytes per row) the quicker the result set can be passed along the wire to your machine. The smaller the result set (ie fewer rows) will also speed things up. I imagine you already know those bits though :-)
Has the query itself been tuned Oracle side to see what the performance is like?