0

I have a sql query which returns approx 500k records. I need to implement pagination so that I can get the records in steps of 1000.

I am trying to implement solution given in the post Paging with Oracle.

I have a question, is using rownum safe option as its temporary(A ROWNUM value is not assigned permanently to a row)?

Assume I run sql query with pageNo = 10 and pagesize = 1000. If i run this query 10 times, I am not getting same set of records.

Is there a better way to achieve this? I wanted to get same set of records every time i run query for particular pageno and pagesize.

Community
  • 1
  • 1
Prateek Shrivastava
  • 380
  • 1
  • 3
  • 13
  • 1
    I cannot modify the tables, they are owned by different vendor. I can only run select sql query – Prateek Shrivastava Jun 24 '16 at 15:35
  • Would there be anything wrong with using an integer identity column, which would place a permanent id on each column? – Tim Biegeleisen Jun 24 '16 at 15:35
  • 1
    In a highly-active *(read:* "real-world production ...") database table, the row-number approach might not work, because rows are being added to the table all the time, and the order of rows is difficult to predict. Somehow, you need to get primary-key values involved. You might, for example, use the above technique to take a slice of values that's larger than what you need, then look for the last "last primary key" value that you obtained last time, to determine your next starting point, which may-or-may-not be at the start of the range of rows that you are given for any particular query. – Mike Robinson Jun 24 '16 at 15:36
  • ... another important consideration is that, especially on a busy table, your approach must be efficient and, so to speak, "a good citizen." Make sure that your approach is easy on the database . . . that it doesn't consider too-many rows or do too-big of a sort, etc. – Mike Robinson Jun 24 '16 at 15:38
  • Is the table set in stone? If not, you won't get the same set of records if you run the query 10 times, no matter what method you use. In a real-life table, rows are added, updated, and deleted all the time; you can't get the "same set of records." What is the real problem you are trying to solve? – mathguy Jun 24 '16 at 15:43
  • We are replacing legacy system with new processing engine. We need to do one time database load, with transformation of data from Legacy system. My approach is to pick range of records from legacy system database, do transformation and upload it to new system. – Prateek Shrivastava Jun 24 '16 at 15:47
  • Assuming you apply an `order by` before you generate the `rownum` and you order by something that is monotonically increasing while you're fetching data, you should get a stable set of rows. If you're not getting a stable set of rows, I would expect your query is incorrect. But it doesn't make sense to fetch data like this to load data into a new system. Just run the query, fetch however many rows you want, do whatever transform you want, load it into the new system, and then fetch the next set of rows from the cursor. – Justin Cave Jun 27 '16 at 14:28

0 Answers0