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 ) ?

Solution 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.

Solution 2

"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

Solution 3

select * 
  from ( select /*+ FIRST_ROWS(n) */   a.*,
      ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

Step 1: your query with order by

Step 2: select a.*, ROWNUM rnum from ()a where ROWNUM <=:MAX_ROW_TO_FETCH

Step 3: select * from ( ) where rnum >= :MIN_ROW_TO_FETCH; put 1 in 2 and 2 in 3

Comments

[Gary Myers]

The other alternative is actually grab 100 rows at the start but only show 10 at a time. The javascript only has to hide/reveal table rows. This counts on the users never wanting to page down more than ten times.