0

I need to do SELECT query from java code on table that contains 1kkk rows , so the result set will be near 1kk rows. thats why I need to limit it, and run select query many times and each time to retrieve 10k rows only. After I get 10k rows I update it so in the next SELECT they wont be retrieved. The problem is - in each select oracle looking from first row in the table so it reduces the performance. How can I use OFFSET for avoiding oracle looking from first row and pass over already updated rows?

Demoniac18
  • 23
  • 1
  • 4
  • What are you trying to do? I can't imagine a user browsing through this amount of results. I can't imagine a process working in batches – Rene Sep 08 '11 at 13:16
  • Why do you need to limit the result set for an update? I have regularly run update statements affecting millions of rows at one time before committing. – WoMo Sep 08 '11 at 15:46
  • Is it not possible to identify the rows that have already been updated and ignore them ? maybe store a batch number against the records updated – Kevin Burton Sep 09 '11 at 10:00
  • I am trying to update DB according to some logic. I need to limit because I cant to create result set object that contains more than 1 million rows . The program will fall on HeapSpace size overloaded. – Demoniac18 Sep 10 '11 at 14:26
  • You can use `OFFSET` if you have Oracle 12cR1 or higher: http://stackoverflow.com/a/26051830/1461424 – sampathsris Sep 26 '14 at 10:43

1 Answers1

8

None of these solutions actually improve performance.

You've got a dataset of 250,000 rows and fetch them in batches of 10,000. Unless you have a stateful connection to the database and keep that SELECT statement in progress (in oracle terms this is 'keeping the cursor open') then each select is independent of the last.

As such to fetch the batch from 180,001 to 190,000 it will still have to sort so it can work out the first 190,000 rows. Syntactic sugar such as OFFSET doesn't alter the fundamental rules of maths and logic.

To get the best performance you need to keep the resultset open and just keep fetching rows from it. Don't close it and don't re-issue the select.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • Hi, Gary. I cant even create result set in java code because it will contain 1+ million rows so I need to fetch it in batch of 10.000 rows by using SELECT query many times. And the last times of SELECT will pass over the table from first row. – Demoniac18 Sep 09 '11 at 22:29
  • Connection.setFetchSize allows you to tell the JBDC driver how many rows you want it to fetch at once. Trying to fetch everything into a RowSet will cause problems, sure, but Oracle's JDBC driver is smart enough to not fetch rows that it doesn't need to and I have no problem iterating over a 40 million row query result with one call to Statement.query() and 40 million calls to ResultSet.next() – Steve McKay May 02 '12 at 00:53