This question already has an answer here:
-
How ROWNUM works in pagination query?
3 answers
I want to know about pagination in oracle.In order to do pagination in sql server, there is OFFSET ROWS FETCH NEXT in which order by is mandatory in order to do pagination.
I am trying to figure out out if i want to do pagination in oracle is order by mandatory.
Following are the ways to do pagination in oracle i came to know about:
1) OFFSET n ROWS FETCH NEXT m ROWS ONLY :
Order by is mandatory here is guess.
2) RowNum:
With this i am not sure whether orderby is mandatory or not for pagination
with RowNum.
I am trying to find out answers of below questions and i will appreciate if someone could please help me with the answers :
1) Is order by mandatory with OFFSET n ROWS FETCH NEXT m ROWS ONLY?
2) Is order by mandatory with RowNum?
If i have a select query like this:
select Id,Amount from source
3) How to create pagination in above sql query using RowNum?
1) Is order by mandatory with OFFSET n ROWS FETCH NEXT m ROWS ONLY?
Syntactically not, semantically it is!
Reason: if you don't add an ORDER BY clause, the database may return the orders in any order. Now if you execute the query first for the first page, you'll get them in any order. The next time you execute the query to fetch the next page may return the orders in any other row.
Therefore you need on ORDER BY clause that establishes a definite order of rows (so that no row is a peer with another row). In practice, you should always include something unique/primary key in the ORDER BY clause to be on the safe side. (you can still use non-unique in the ORDER BY clause — even as leading columns).
e.g.
ORDER BY time_stamp DESC, id DESC
This is a logical requirement for all types of pagination that execute separate queries for each page.
2) Is order by mandatory with RowNum?
Yes, see above.
3) How to create pagination in above sql query using RowNum?
Neither OFFSET nor ROWNUM alone are good enough to implement stable pagination.
Think about this: What if a new row is inserted after you have fetched the first page and before you fetch the second page?
There is another way to implement stable pagination called key-set pagination.
The main idea is not to skip "seen rows" by telling the database how many rows to skip and hoping no rows were added in the meanwhile, but to use a unique identification of which rows have already been seen and which not.
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLY
Remember that you need an ORDER BY that establishes a definitie order anyway. You can use these columns to pinpoint the place until where you have received the data before.
Read more about this method at my website:
http://use-the-index-luke.com/no-offset
