0

I have a 11g oracle database and my table has close to a million rows. I need to do pagination to show the records to the user and I figured out a few approaches with the help of this article.

But in every approach, there is an order by statement and for my application, ordering is not really necessary.

So if I remove order by and then do pagination,

  • What are the advantages/disadvantages when compared to pagination with ordering? May be query execution time etc.?
  • Will there be a possibility that oracle will fetch the same row/rows in multiple pages? Because as per this and this, oracle randomly fetches the rows and sends if there is no order by statement.

Update - 1

  • Every user should be able to view all the data (million rows)

  • The data fetched consists of 15-20 columns of varchar(255) and numbers

Community
  • 1
  • 1
Chinni
  • 1,199
  • 1
  • 14
  • 28
  • I think you should use `ORDER BY` if you choose to have pagination. As you add new records to the table, whatever order Oracle was using could change suddenly. There is no concept of internal order in most RDBMS. – Tim Biegeleisen Jun 02 '16 at 09:02
  • @Tim Biegeleisen: Yes. Agreed. But for my application, ordering is not at all necessary. My main concern without ordering is I'm not sure if oracle gives me the same row (or) rows in multiple pages since there is no order. – Chinni Jun 02 '16 at 09:16
  • 1
    If there is no order, does the concept of a page really make any sense? You should use `ORDER BY` IMO. For some parts of Oracle, e.g. analytic functions, you are required to have an order. – Tim Biegeleisen Jun 02 '16 at 09:21
  • 9
    The ordering is there to provide consistent results between calls; whatever you order by needs to give a deterministic set. Otherwise if you ask for rows 1-10 and then have a separate query to ask for rows 11-20 they *may* both contain the same rows - it depends what plan Oracle uses for both (sort of independent) queries. But it depends how you're showing the records; if you open a cursor and fetch 10 rows, then fetch another 10 *from the same cursor result set* then you don't need to order to avoid duplicates. So, what is your application actually doing? – Alex Poole Jun 02 '16 at 09:21
  • @Tim Biegeleisen: Yes. For my application, pagination makes sense though there is no ordering. My main reason to do pagination is to fetch limited data since there are close to a million rows in my table. – Chinni Jun 02 '16 at 09:34
  • @Alex Poole: Thank you for clarifying that. For my application, we don't want to use cursors because we have a huge traffic and we don't want to open too many cursors at the same time. – Chinni Jun 02 '16 at 09:37
  • Then a user's request for page 2 is effectively independent of their query for page 1. As well as Oracle potentially picking a different plan, the data may have changed in between. You have high traffic and all your visitors are paging through all million rows? If not then how big is the subset each user is looking at, how is it filtered, and is there really nothing it would be sensible to order by? Overhead of ordering a subset of data is less of an issue than ordering all million rows. (Explaining the kind of data, output and user interaction in the question might be helpful, for context) – Alex Poole Jun 02 '16 at 10:30
  • @Alex Poole: Please check my Update-1 in the question. If there is a chance of oracle repeating the rows in multiple pages, then there is no point in me paginating without sorting. So, I'll use `order by` – Chinni Jun 02 '16 at 11:51

0 Answers0