oracle faster paging query

Question

I have two paging query that I consider to use.

First one is

SELECT * FROM ( SELECT rownum rnum, a.* from (
    select * from members
) a WHERE rownum <= #paging.endRow# ) where rnum > #paging.startRow#

And the Second is

SELECT * FROM ( SELECT rownum rnum, a.* from (
    select * from members
) a ) WHERE rnum BETWEEN #paging.startRow# AND #paging.endRow#

how do you think which query is the faster one?

Optimizer will probably see them as the same, but you need to see the plan. — Michael Ballent, Jul 27 '12 at 05:30

Alessandro Rossi · Answer 1 · 2019-07-25T10:32:45.573

15

I don't actually have availability of Oracle now but the best SQL query for paging is the following for sure

select *
from (
        select rownum as rn, a.*
        from (
                select *
                from my_table
                order by ....a_unique_criteria...
            ) a
    )
where rownum <= :size
    and rn >  (:page-1)*:size

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

To achieve a consistent paging you should order rows using a unique criteria, doing so will avoid to load for page X a row you already loaded for a page Y ( !=X ).

EDIT:

1) Order rows using a unique criteria means to order data in way that each row will keep the same position at every execution of the query

2) An index with all the expressions used on the ORDER BY clause will help getting results faster, expecially for the first pages. With that index the execution plan choosen by the optimizer doesn't needs to sort the rows because it will return rows scrolling the index by its natural order.

3) By the way, the fastests way to page result from a query is to execute the query only once and to handle all the flow from the application side.

edited Jul 25 '19 at 10:32

answered Jul 27 '12 at 17:35

Alessandro Rossi

2,432
17
24

This is an old answer, but it seems to be missing something in the where clause. `rownum <= :size` will only ever be able to return the first page. Should it not be `rownum <= :page * :size`? – Devin H. Apr 04 '16 at 17:51
@Devin H: It depends by how you prefer to start counting. If you start counting from 1(one) it's right to use (page - 1), if you want to start from 0(zero) you must use :page without subctracting 1 to it. – Alessandro Rossi Apr 07 '16 at 19:50
@AlessandroRossi, this is super fast. However, how should I order rows using a unique criteria - a simple "Order by id" slows things up significantly ? – Lucy82 Jul 25 '19 at 09:17
@AlessandroRossi, I don't understand quite what you wrote. I would be happy If I could order data in a place where you mentioned ORDER BY. However by placing there my query is slow again. So how could I fix, maybe some example ? – Lucy82 Jul 25 '19 at 11:35
@Lucy82: I added 3 things with the past edit, what are the ones you didn't understand? If you're able to point exactly what you're talking about, I may also try to give more details. This answer is based on what the OP asked 7 years ago and is specific for what he asked that time. If you're having particular performance issues they may also depend by something not mentioned in his question. So it would be better for you to ask a new question with all the needed details about your case. In this way it may be easier for you the get the the answer that best fits your needs. – Alessandro Rossi Jul 25 '19 at 12:50
@Alessandro Rossi, I'm not sure on your 1st and 2nd edit. I'm using this suggestion for paginating query result, but without order by clause I get data like Oracle chooses (unsorted) , though very fast. So I'm wondering If that could be fixed too, without loosing too much on execution speed. – Lucy82 Jul 25 '19 at 13:47

score 9 · Accepted Answer · answered Jul 27 '12 at 05:39

Take a look at the execution plans, example with 1000 rows:

SELECT *
  FROM (SELECT ROWNUM rnum
              ,a.*
          FROM (SELECT *
                  FROM members) a
         WHERE ROWNUM <= endrow#)
 WHERE rnum > startrow#;

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  1000 | 39000 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                |         |  1000 | 39000 |     3   (0)| 00:00:01 |
|   2 |   COUNT              |         |       |       |            |          |
|*  3 |    FILTER            |         |       |       |            |          |
|   4 |     TABLE ACCESS FULL| MEMBERS |  1000 | 26000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RNUM">"STARTROW#")
   3 - filter("MEMBERS"."ENDROW#">=ROWNUM)

And 2.

SELECT *
  FROM (SELECT ROWNUM rnum
              ,a.*
          FROM (SELECT *
                  FROM members) a)
 WHERE rnum BETWEEN startrow# AND endrow#;

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |  1000 | 39000 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |         |  1000 | 39000 |     3   (0)| 00:00:01 |
|   2 |   COUNT             |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL| MEMBERS |  1000 | 26000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RNUM"<="ENDROW#" AND "RNUM">="STARTROW#")

Out of that I'd say version 2 could be slightly faster as it includes one step less. But I don't know about your indexes and data distribution so it's up to you to get these execution plans yourself and judge the situation for your data. Or simply test it.

@PatrickJeon - if you didn't know about execution plans, read some docs before you dive into this. In general you cannot simply count the steps as I did for your simple one-table-no-indexes example. Especially take a look at the "Cost" column, that's the primary indicator. — GWu, Jul 27 '12 at 06:01

score 0 · Answer 3 · edited May 23 '17 at 12:31

A already answered in here But let me copypaste.

Just want to summarize the answers and comments. There are a number of ways doing a pagination.

Prior to oracle 12c there were no OFFSET/FETCH functionality, so take a look at whitepaper as the @jasonk suggested. It's the most complete article I found about different methods with detailed explanation of advantages and disadvantages. It would take a significant amount of time to copy-paste them here, so I want do it.

There is also a good article from jooq creators explaining some common caveats with oracle and other databases pagination. jooq's blogpost

Good news, since oracle 12c we have a new OFFSET/FETCH functionality. OracleMagazine 12c new features. Please refer to "Top-N Queries and Pagination"

You may check your oracle version by issuing the following statement

SELECT * FROM V$VERSION

oracle faster paging query

3 Answers3

Linked