Best pagination practice in Oracle?

Question

Problem: I need to write a stored procedure (s) that will return the result set of one page of lines and the number of complete lines.

Solution A: I create two stored procedures that return a result set for one page and another that returns scalar full rows. The plan of explanation says that the first sproc has a value of 9, and the second - 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 one sproc, adding the same number of TotalRows to each row in the result set. This solution seems hacky, but has a cost of 9 and only one sproc, so I tend 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 above solutions is most often used in practice? Are any of them considered simply wrong? Please note that my DB is and will remain relatively small (less than 10 GB).

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

Answer 1

If you are already using analytics ( ROW_NUMBER() OVER... ), then adding another analytic function in the same section will lead to an insignificant cost of the request.

On the other hand, there are many other ways to rownum , one of them uses 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 better if you have the corresponding index in the order column. In this case, it may be more efficient to use two queries (one for the total number of rows, one for the result).

Both methods are suitable, but in general, if you need both the number of rows and the pagination set, using analytics is more efficient since you only query rows once.

Answer 2

This can help:

  SELECT * FROM ( SELECT deptno, ename, sal, ROW_NUMBER() OVER (ORDER BY ename) Row_Num FROM emp) WHERE Row_Num BETWEEN 5 and 10; 

Answer 3

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

Example. Suppose you have a tab table tab from which you want to extract data based on a DATE dt data type column in descending order paginated.

 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 - OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY 5 rows

page_no = 2 page_size = 5

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY - OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY next 5 lines

and so on.

Reference 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

Answer 4

Try the following:

 select * from ( select * from "table" order by "column" desc ) where ROWNUM > 0 and ROWNUM <= 5; 

Answer 5

Sorry, this works with sorting:

 SELECT * FROM (SELECT ROWNUM rnum,a.* FROM (SELECT * FROM "tabla" order by "column" asc) a) WHERE rnum BETWEEN "firstrange" AND "lastrange"; 

Answer 6

I also ran into a similar problem. I tried all of the above solutions and no one gave me the best performance. I have a table with millions of records, and I need to display them on the screen on pages 20. I decided to do this below.

• Add a new column ROW_NUMBER to the table.
• Make the column a primary key or add a unique index to it.
• Use a population program (in my case Informatica) to populate the rownum column.
• Retrieve records from a table using instructions. (SELECT * FROM TABLE WHERE ROW_NUMBER BETWEEN LOWER_RANGE AND UPPER_RANGE).

This method is effective if we need to make an unconditional selection of a partition into a huge table.

Answer 7

A clean way to organize your SQL code can be with the WITH statement.

The shortened version also implements the total number of results and the total number of pages .

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 result set with two more fields:

• TOTAL_ROWS with full lines of your full SELECTION
• RN record line number

It takes 2 parameters :page_size and :page_number to :page_number your SELECTION

Smaller version

Selection implements already ROW_NUMBER()

 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)