5

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?

I Love Stackoverflow
  • 6,738
  • 20
  • 97
  • 216
  • Think about if you have only 10 data and you do pagination for 2 row. now when you try to get next 2 row it will come any of your 10 data. there are certain changes to repeat your row data but if you do orderby you will get always new row to show. – krishn Patel Feb 05 '18 at 10:38
  • Check [subquery](https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF55225). Looks like `ORDER BY` is not mandatory. – Wernfried Domscheit Feb 05 '18 at 10:40
  • @WernfriedDomscheit Still there is order by clause mention – I Love Stackoverflow Feb 05 '18 at 10:41
  • Yes, but according graph you can bypass it. Check [documentation](https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABHFGAA): *For consistent results, specify the order_by_clause to ensure a deterministic sort order.* So, you can omit but then the result is not deterministic anymore. Please read documentation first - ask at SO second. – Wernfried Domscheit Feb 05 '18 at 10:45
  • @WernfriedDomscheit So consider i have 10 data with Id=1,2,3 resp and i get the first 2 record(Id=1,2) and now if i am trying to get next 2 records(3,4) so in this case it is not fixed that i will get 3,4.I can also get 1,2 as because order by is missing? – I Love Stackoverflow Feb 05 '18 at 10:48
  • 1
    @User - yes, because *it isn't deterministic*. The order-by isn't mandatory syntactically, but it is necessary logically. – Alex Poole Feb 05 '18 at 11:16
  • @AlexPoole But RowNum always assign unique row number for each row and if i maintain pagesize and pageno values efficiently with rownum as shown here (https://stackoverflow.com/questions/241622/paging-with-oracle)then how can i get inconsistent result? – I Love Stackoverflow Feb 05 '18 at 11:19
  • You can't with that linked query - *because* it has an `order by`. I'm not really sure what you're asking. – Alex Poole Feb 05 '18 at 11:21
  • @AlexPooleBut RowNum always assign unique row number for each row and if i maintain pagesize and pageno values efficiently with rownum but without order by then how can i get inconsistent result? – I Love Stackoverflow Feb 05 '18 at 11:24
  • rownum is assigned for each line in the output as it is returned. Without an order by (and assuming a consistent dataset, i.e. no DML, truncates, etc.), you cannot guarantee that the row labelled as 1 in one execution will be the same as that in a different execution. – Boneist Feb 05 '18 at 12:21

1 Answers1

7

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

Markus Winand
  • 8,371
  • 1
  • 35
  • 44