Paging with Oracle and sql server and generic paging method

Question

I want to implement paging in a gridview or in an html table which I will fill using ajax. How should I write queries to support paging? For example if pagesize is 20 and when the user clicks page 3, rows between 41 and 60 must be shown on table. At first I can get all records and put them into cache but I think this is the wrong way. Because data can be very huge and data can be change from other sessions. so how can I implement this? Is there any generic way ( for all databases ) ?

Answer 1

Unfortunately, the methods for restricting the range of rows returned by a query vary from one DBMS to another: Oracle uses ROWNUM (see ocdecio's answer), but ROWNUM won't work in SQL Server.

Perhaps you can encapsulate these differences with a function that takes a given SQL statement and first and last row numbers and generates the appropriate paginatd SQL for the target DBMS - i.e. something like:

sql = paginated ('select empno, ename from emp where job = ?', 101, 150)

which would return

'select * from (select v.*, ROWNUM rn from ('
 + theSql
 + ') v where rownum < 150) where rn >= 101'

for Oracle and something else for SQL Server.

However, note that the Oracle solution is adding a new column RN to the results that you'll need to deal with.

Answer 2

I believe that both have a ROWNUM analytic Function. Use that and you'll be identical.

In Oracle it is here

ROW_NUMBER

Yep, just verified that ROW_NUMBER is the same function in both.

Answer 3

If the expected data set is huge, I'd recommend to create a temp table, a view or a snapshot (materialized view) to store the query results + a row number retrieved either using ROWNUM or ROW_NUMBER analytic function. After that you can simply query this temp storage using row number ranges. Basically, you need to separate the actual data fetch from the paging.

Answer 4

"Because...data can be change from other sessions." What do you want to happen for this ?

For example, user gets the 'latest' ten rows at 10:30.

At 10:31, 3 new rows are added (so those ten being view by the user are no longer the latest).

At 10:32, the user requests then 'next' ten entries.

Do you want that new set to include those three that have been bumped from 8/9/10 down to 11/12/13 ? If not, in Oracle you can select the data as it was at 10:30

SELECT * FROM table_1 as of timestamp (timestamp '2009-01-29 10:30:00');

You still need the row_number logic, eg

 select * from
    (SELECT a.*, row_number() over (order by hire_date) rn
    FROM hr.employees as of timestamp (timestamp '2009-01-29 10:30:00') a)
 where rn between 10 and 19

Answer 5

There is no uniform way to ensure paging across various RDBMS products. Oracle gives you rownum which you can use in where clause like:

where rownum < 1000

SQL Server gives you row_id( ) function which can be used similar to Oracle's rownum. However, row_id( ) isn't available before SQL Server 2005.

Answer 6

As others have suggested, you can use rownum in Oracle. It's a little tricky though and you have to nest your query twice.

For example, to paginate the query

select first_name from some_table order by first_name

you need to nest it like this

select first_name from
  (select rownum as rn, first_name from
    (select first_name from some_table order by first_name)
  ) where rn > 100  and rn <= 200

The reason for this is that rownum is determined after the where clause and before the order by clause. To see what I mean, you can query

select rownum,first_name from some_table order by first_name

and you might get

4   Diane
2   Norm
3   Sam
1   Woody

That's because oracle evaluates the where clause (none in this case), then assigns rownums, then sorts the results by first_name. You have to nest the query so it uses the rownum assigned after the rows have been sorted.

The second nesting has to do with how rownum is treated in a where condition. Basically, if you query "where rownum > 100" then you get no results. It's a chicken and egg thing where it can't return any rows until it finds rownum > 100, but since it's not returning any rows it never increments rownum, so it never counts to 100. Ugh. The second level of nesting solves this. Note it must alias the rownum column at this point.

Lastly, your order by clause must make the query deterministic. For example, if you have John Doe and John Smith, and you order by first name only, then the two can switch places from one execution of the query to the next.

There are articles here http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html and here http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html. Now that I see how long my post is, I probably should have just posted those links...