Initial attempts at getting a very simple pagination, using fetch n rows and then a subsequent call with offset, gives overlapping entries in Oracle.
I was expecting the following to give me two unique sets of results. 1-100 and then 101-200 of the results that would have been returned if the first line had been set with a limit of 200.
select * from "APPR" /*+ index(APPR APPR_IDX01) */ where ("APPROVER" = 'A') or ("APPROVER" > 'A') order by "APPROVER" fetch first 100 rows only ;
select * from "APPR" /*+ index(APPR APPR_IDX01) */ where ("APPROVER" = 'A') or ("APPROVER" > 'A') order by "APPROVER" offset 100 rows fetch next 100 rows only ;
So if there are 150 items for approver A the first results should be:
- A, item1
- ….
- A, item100
The subsequent call (offset by 100) giving
- A, item101
- …
- A, item150
- B, item1
- B, item2
- ….
- B, item201
Unfortunately the second set contains some entries from the first batch of values. Probably a really silly error, but I can’t find an explanation as to why this should happen.
—- Updated as a result of comments The Primary key consists of Approver and several other fields which together form a composite and unique primary.
The code will be called through ODBC and will be used on Oracle and MySQL back-end.
Advertisement
Answer
In Oracle, if you make “order by” to a column containing same values (like you have – ‘A’, ‘A’, ‘A’ …) the order of records inside ‘A’ values will be random.
Please try to change your queries to … order by "APPROVER", rowid …