34

Problem: I need write stored procedure(s) that will return result set of a single page of rows and the number of total rows.

Solution A: I create two stored procedures, one that returns a results set of a single page and another that returns a scalar -- total rows. The Explain Plan says the first sproc has a cost of 9 and the second has a cost of 3.

SELECT  *
FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY D.ID DESC ) AS RowNum, ...
        ) AS PageResult
WHERE   RowNum >= @from
    AND RowNum < @to
ORDER BY RowNum

SELECT  COUNT(*)
FROM    ...

Solution B: I put everything in a single sproc, by adding the same TotalRows number to every row in the result set. This solution feel hackish, but has a cost of 9 and only one sproc, so I'm inclined to use this solution.

SELECT * 
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY D.ID DESC  ) RowNum, COUNT(*) OVER () TotalRows,
WHERE RowNum >= from
        AND RowNum < to
ORDER BY RowNum;

Is there a best-practice for pagination in Oracle? Which of the aforementioned solutions is most used in practice? Is any of them considered just plain wrong? Note that my DB is and will stay relatively small (less than 10GB).

I'm using Oracle 11g and the latest ODP.NET with VS2010 SP1 and Entity Framework 4.4. I need the final solution to work within the EF 4.4. I'm sure there are probably better methods out there for pagination in general, but I need them working with EF.

Howie
  • 2,760
  • 6
  • 32
  • 60
  • 2
    Pagination in EF is database agnostic. – Robert Harvey Dec 06 '12 at 06:36
  • Yeah, but I just wanted to make it clear, that I don't want to use ODP.NET- or ADO.NET-specific code, but would rather stay on high-level. – Howie Dec 06 '12 at 06:44
  • 1
    Tom Kyte wrote an article on limiting resultsets using rownum: http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html It may answer some of your questions. – Rene Dec 06 '12 at 07:37

7 Answers7

39

If you're already using analytics (ROW_NUMBER() OVER ...) then adding another analytic function on the same partitioning will add a negligible cost to the query.

On the other hand, there are many other ways to do pagination, one of them using rownum:

SELECT * 
  FROM (SELECT A.*, rownum rn
          FROM (SELECT *
                  FROM your_table
                 ORDER BY col) A
         WHERE rownum <= :Y)
 WHERE rn >= :X

This method will be superior if you have an appropriate index on the ordering column. In this case, it might be more efficient to use two queries (one for the total number of rows, one for the result).

Both methods are appropriate but in general if you want both the number of rows and a pagination set then using analytics is more efficient because you only query the rows once.

JGFMK
  • 8,425
  • 4
  • 58
  • 92
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • 2
    See Tom Kyte "[On ROWNUM and Limiting Results](http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html)" in Oracle Magazine (Sept/Oct 2006). – Ludovic Kuty Feb 28 '16 at 14:13
  • 1
    I have issues with syntax * followed by comma on second line. Doesn't seem to like syntax. You need A.* instead, then A after col) – JGFMK Apr 27 '18 at 17:11
  • Why did u filtered rownum in two queries instead of one? Could be "WHERE rownum <= :Y AND rownum >= :X". Does it gives poor performance? – Iúri dos Anjos Dec 19 '18 at 14:12
  • 1
    @IúridosAnjos: The [Tom Kyte article](http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html) suggested by [Ludovic Kuty](https://stackoverflow.com/users/452614/ludovic-kuty) explains why it wouldn't work: `rownum >= 2` is always FALSE – Vincent Malgrat Dec 19 '18 at 15:08
  • @VincentMalgrat its not if you add the "ROWNUM" column in the inner select (the one without the WHERE clause) – Iúri dos Anjos Dec 19 '18 at 19:21
  • 1
    @IúridosAnjos: the inner `SELECT` is used to sort and can not contain `ROWNUM` because `ROWNUM` is computed before sorting. [Here's an example with dbfiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=9fbc233cc4c70a0d2b9e4c21618ffde9). Of course it's also explained in the [Oracle Magazine article](http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html) which I once more encourage you to read :) – Vincent Malgrat Dec 20 '18 at 08:55
  • @VincentMalgrat yes, you're right! Thanks. ;) Though you could use 2 selects only, using ROW_NUMBER() OVER (ORDER BY col) on the inner select instead of the order by itself, and them (outer select) filter both >= and <=. – Iúri dos Anjos Dec 20 '18 at 14:14
  • Updated link to [Tom Kyte article](https://asktom.oracle.com/Misc/oramag/on-rownum-and-limiting-results.html) – strongbad03 Feb 23 '23 at 20:02
8

In Oracle 12C you can use limit LIMIT and OFFSET for the pagination.

Example - Suppose you have Table tab from which data needs to be fetched on the basis of DATE datatype column dt in descending order using pagination.

page_size:=5

select * from tab
order by dt desc
OFFSET nvl(page_no-1,1)*page_size ROWS FETCH NEXT page_size ROWS ONLY;

Explanation:

page_no=1 page_size=5

OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY - Fetch 1st 5 rows only

page_no=2 page_size=5

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY - Fetch next 5 rows

and so on.

Refrence Pages -

https://dba-presents.com/index.php/databases/oracle/31-new-pagination-method-in-oracle-12c-offset-fetch

https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1#paging

Tajinder
  • 2,248
  • 4
  • 33
  • 54
  • The sad thing is the lack of COUNT(*) as some sort of variable o built-in column with the total records with the given criteria. I understand that it could cost, but developers will do it anyway in order to determine how many pages we have in total. – zion May 13 '21 at 12:10
6

This may help:

   SELECT * FROM 
     ( SELECT deptno, ename, sal, ROW_NUMBER() OVER (ORDER BY ename) Row_Num FROM emp)
     WHERE Row_Num BETWEEN 5 and 10;
Art
  • 199
  • 2
  • 2
    This is exactly what the OP has in the question... you'll have to explain _why_ it will help as the OP is looking for the most performant solution. Why is this better than using rownum and in what situations? Do you have any benchmarks? – Ben Dec 06 '12 at 18:15
  • 1
    Well, this is how I understood the question. I hope I got it right. The diff between ROWNUM and ROW_NUMBER() (in Oracle) is that first is pseudocolumn and last is analytic function. You can use both. But the ROWNUM is not always work with ORDER BY.Try adding Order by with ROWNUM and you will see the difference. And using analytic functions is best and up to date and generally improves the performance. You can read more about ROWNUM and ROW_NUMBER() in documentation. Thank you. – Art Dec 06 '12 at 19:34
  • 1
    Yes, you're correct. The rownum predicate will return a random value when used in conjuction with an ORDER BY unless the ordering is done inside a sub-select. However, my point was that your answer does not answer the question. It was a comment on whether it would work. Analytic functions though "recent" are, in this case, _not_ necessarily more efficient than `rownum` as they require additional scans of the table whereas `rownum` does not (as explained in Vincent's answer). – Ben Dec 06 '12 at 19:39
1

A clean way to organize your SQL code could be trough WITH statement.

The reduced version implements also total number of results and total pages count.

For example

WITH SELECTION AS (
    SELECT FIELDA, FIELDB, FIELDC FROM TABLE), 
NUMBERED AS (
    SELECT 
    ROW_NUMBER() OVER (ORDER BY FIELDA) RN, 
    SELECTION.*
    FROM SELECTION)
SELECT
    (SELECT COUNT(*) FROM NUMBERED) TOTAL_ROWS,
    NUMBERED.*
FROM NUMBERED
WHERE 
    RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)

This code gives you a paged resultset with two more fields:

  • TOTAL_ROWS with the total rows of your full SELECTION
  • RN the row number of the record

It requires 2 parameter: :page_size and :page_number to slice your SELECTION

Reduced Version

Selection implements already ROW_NUMBER() field

WITH SELECTION AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY FIELDA) RN,
        FIELDA, 
        FIELDB, 
        FIELDC 
    FROM TABLE) 
SELECT
    :page_number PAGE_NUMBER,
    CEIL((SELECT COUNT(*) FROM SELECTION ) / :page_size) TOTAL_PAGES,
    :page_size PAGE_SIZE,
    (SELECT COUNT(*) FROM SELECTION ) TOTAL_ROWS,
    SELECTION.*
FROM SELECTION 
WHERE 
    RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)
Stefano Giraldi
  • 1,211
  • 2
  • 13
  • 22
0

Try this:

select * from ( select * from "table" order by "column" desc ) where ROWNUM > 0 and ROWNUM <= 5;
Stonz2
  • 6,306
  • 4
  • 44
  • 64
Carlos
  • 17
  • 1
  • 4
    Good answers accompany code samples with an explanation for future readers. While the person asking this question may understand your answer, explaining how you arrived at it will help countless others. – Stonz2 Aug 19 '14 at 17:51
  • hmm... doesn't work if I change rownum > 0 to rownum > 1. I don't know why for now... – Pavel Biryukov Feb 22 '17 at 13:30
0

Sorry, this one works with sorting:

SELECT * FROM (SELECT ROWNUM rnum,a.* FROM (SELECT * FROM "tabla" order by "column" asc) a) WHERE rnum BETWEEN "firstrange" AND "lastrange";
Infinite Recursion
  • 6,511
  • 28
  • 39
  • 51
Carlos
  • 17
  • 1
  • 1
    Hello and welcome to StackOverflow. Please note that rather than creating a new answer, you should edit your existing answer if you have an improvement. Thanks! – Fabian Fagerholm Aug 19 '14 at 18:22
0

I also faced a similar issue. I tried all the above solutions and none gave me a better performance. I have a table with millions of records and I need to display them on screen in pages of 20. I have done the below to solve the issue.

  1. Add a new column ROW_NUMBER in the table.
  2. Make the column as primary key or add a unique index on it.
  3. Use the population program (in my case, Informatica), to populate the column with rownum.
  4. Fetch Records from the table using between statement. (SELECT * FROM TABLE WHERE ROW_NUMBER BETWEEN LOWER_RANGE AND UPPER_RANGE).

This method is effective if we need to do an unconditional pagination fetch on a huge table.

Ram Kiran
  • 1