SELECT query with OFFSET in ORACLE

0
votes

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?

1
sqloracleoffset
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
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. - Wolf
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
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
You can use OFFSET if you have Oracle 12cR1 or higher: stackoverflow.com/a/26051830/1461424 - sampathsris

1 Answers

8
votes

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.