Best practice for pagination in Oracle?

Best practice for pagination in Oracle?

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.

Answer by Vincent Malgrat for Best practice for pagination in Oracle?

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 *, rownum rn            FROM (SELECT *                    FROM your_table                   ORDER BY col)           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.

Answer by Art for Best practice for pagination in Oracle?

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;  

Answer by Carlos for Best practice for pagination in Oracle?

Try this:

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

Answer by Carlos for Best practice for pagination in Oracle?

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";  

Answer by Ram Kiran for Best practice for pagination in Oracle?

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.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72