6

There are plenty of questions on stackoverflow on how to perform pagination correctly, for oracle the most popular answer is something like this:

select * from (
    select row_.*, rownum rownum_ 
    from (select * from some_table) row_ 
    where rownum <= N) 
where rownum_ > M;

I've seen this query everywhere and Hibernate also generates it for pagination. It's probably ok for most cases but it requires full table scan and slows down significantly on huge amounts of data.

There is a hint that's supposed to help selecting first N rows

/*+ FIRST_ROWS(5000) */

but it doesn't help in case of selecting second page and seems to use full scan also, at least that's what "explain plan" says to me.

To deal with this problem I'm currently implementing custom pagination - reading ids of all rows in a table and splitting them on ranges so that pagination query can look something like this:

select * from some_table where id between N and M;

I was hoping to find a vendor solution to this problem but haven't succeeded so far.

So, the question is - am I reinventing the wheel or there is realy no way to implement pagination on oracle without full scan?

Upd: in Oracle 12c they introduced a new syntax for paging:

OFFSET N ROWS FETCH NEXT M ROWS ONLY;

I've tried explain plan on this and it seems to be just an alias for

select * from (
    select row_.*, rownum rownum_ 
    from (select * from some_table) row_ 
    where rownum <= N) 
where rownum_ > M;

UPD2: just found a similar question - Oracle and Pagination looks like I've been inattentive while searching for duplicates before. So, most probably the answer on my question is negative but still, maybe something has changed since then...

Community
  • 1
  • 1
Ruslan Sverchkov
  • 440
  • 3
  • 15
  • 1
    Well, you have no constraint in your select, so it is going to do a full table scan. Also, don't you need some order by in the inner select statement? – OldProgrammer Mar 24 '15 at 12:47
  • The thing is that I don't need any constraints, I want to traverse the whole table by chunks) Using cursor could help but I want to do this in separate transactions, probably with server shutdowns between them. – Ruslan Sverchkov Mar 24 '15 at 12:49
  • Concerning ordering you're right, there should be "order by id" – Ruslan Sverchkov Mar 24 '15 at 13:02
  • 1
    If you don't have a column that indicates position in the table (ie. a static row number), you don't really have any option other than to do a full table scan. Especially if you want to traverse the entire table. Although I wonder why you think separate transactions is the way to go - what if you have extra rows being added in between your selects? – Boneist Mar 24 '15 at 13:23
  • 3
    In my considerable experience on this, Oracle does not require a full table scan for paging. As has been said, you will want an order by in your SQL, otherwise you won't get back consistent paging and it will be meaningless. The column you are sorting on has to have a btree index and the results returned must not be too sparse in the table. Plus Oracle has a habit of doing FTS even for selects that return a small percentage of the table. But - yes - it can do a range scan if the wind is in the right direction and you sacrificed the right kind of chicken. – rghome Mar 24 '15 at 14:39
  • to Boneist: separate transactions have been choosen because of batch nature of the application, I mean it's too expensive to start from the beginning if something went wrong on N-th item. Extra rows being added in between selects is a serious problem, we're going to solve it with cross-transactional locking (implemented as a separate table). Yes, this is complex and error-prone but we have no choise) – Ruslan Sverchkov Mar 24 '15 at 16:26
  • to Boneist: static row number is a very interresting idea though, in my opinion it deserves to be posted as an answer to the question – Ruslan Sverchkov Mar 24 '15 at 16:32
  • Probably you can only index order by column and place that index in tablespace on fast hdd (like ssd) – li-on Apr 15 '15 at 09:15

1 Answers1

1

First of all: A full table scan is not always the devil.

  1. Check at your explain plan the costs of your query without pagination
  2. Check it inside your pagination solution

Also when you do tests, try to use large tables with high values of pagination

Additional points:

  • Pagination without ordering is always dangerous because you cant be sure, in which order Oracle provide your result for your next "page" -> possible not reproducible results
  • Also in an ordered result is it possible, that a "new entry" in a page before your current "view" affects your "next page"

I except, that you like to "store" a query in DB and get the data, page by page, until something changed in the underlined data?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gambotic
  • 824
  • 8
  • 19
  • My solution works incredibly fast compared to pagination with full scan) actually I didn't even manage to wait until the last one is finished, on my data volumes it would took dozens of hours to complete (computed value based on observations). You're right about ordering, we're using it, it's just the example in the question I haven't written correctly. – Ruslan Sverchkov May 22 '15 at 13:52
  • People say a cursor will do for me, that's also true, but it's too expensive to rewrite everything with cursors and refuse benefits of JPA at this phase of the project) – Ruslan Sverchkov May 22 '15 at 13:55
  • Another option would be to select ids I'm interrested in and then perform SELECT ... WHERE ID IN (...), we're using this option too but it's a little bit slower (but still orders of magnutide faster than full scan) – Ruslan Sverchkov May 22 '15 at 13:57
  • Full scan works just fine when we're retrieving the first page, the second one and so on. Put with diving deeper it slows down progressively due to the oracle dynamic row number generation mecanism, on large page numbers the execution time is huge. Maybe this is because I'm doing something wrong (sure of it), but I didn't manage to overcome this effect – Ruslan Sverchkov May 22 '15 at 14:05