Paging with Oracle and SQL Server and the General Swap Method

Question

I want to implement paging in a gridview or in an html table, which I will populate with ajax. How do I write requests to support paging? For example, if the page size is 20, and when the user clicks on page 3, rows from 41 to 60 should be displayed in the table. At first I can get all the records and put them in the cache, but I think this is the wrong way. Because the data can be very large, and the data can be changed from other sessions. so how can i implement this? Is there a general way (for all databases)?

Answer 1

Like others, you can use rownum in Oracle. This is a bit complicated and you need to double enclose your request.

For example, to split a query

select first_name from some_table order by first_name 

you need to put 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 defined after the where clause and before the order by clause. To find out what I mean, you can request

 select rownum,first_name from some_table order by first_name 

and you can get

 4 Diane 2 Norm 3 Sam 1 Woody 

This is because oracle evaluates the where clause (in this case there is nobody), then assigns rownums, and then sorts the results by first_name. You must nest the query so that it uses the rownum assigned after sorting the rows.

The second nesting is related to how rownum is handled in that place. Basically, if you request "where rownum> 100", then you will not get any results. This is a chicken and an egg where he cannot return a single row until he finds rownum> 100, but since he does not return a single row, he never increases rownum, so he never counts to 100. Ugh. The second level of nesting solves this. Note that at this point it should be an alias of the rownum column.

Finally, the order by clause should make the request deterministic. For example, if you have John Doe and John Smith, and you order only by name, then they can switch places from one 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 has been, I probably should have just posted these links ...

Answer 2

Unfortunately, methods to limit the range of rows returned by a query vary from one DBMS to another: Oracle uses ROWNUM (see ocdecio answer), but ROWNUM will not work in SQL Server.

Perhaps you can encapsulate these differences using a function that accepts the SQL job and the numbers of the first and last lines and generates the appropriate paginatd SQL for the target DBMS - for example:

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

which will 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 adds a new RN column to the results you have to deal with.

Answer 3

I believe that both have the analytic function ROWNUM. Use this and you will be identical.

In Oracle, it is here

ROW_NUMBER

Yes, just make sure ROW_NUMBER is the same function in both.

Answer 4

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

For example, the user gets the "last" ten lines at 10:30.

At 10:31, 3 new lines were added (so those that were viewed by the user are no longer the last). A.

At 10:32 a user requests the “next” ten records.

Do you want this new set to include the three that were removed from 8/9/10 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 row_number logic like

  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

 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 request with order

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

Answer 6

If the expected dataset is huge, I would recommend creating a temporary table, view or snapshot (materialized view) to store the query results + row number obtained using the analytic function ROWNUM or ROW_NUMBER. After that, you can simply request this temporary storage using line number ranges. Basically, you need to separate the actual data selection from the search call.

Answer 7

There is no single way to provide paging for various RDBMS products. Oracle gives you rownum, which you can use in the place where:

 where rownum < 1000 

SQL Server provides you with the row_id () function, which can be used similarly to Oracle rownum. However, row_id () is not available until SQL Server 2005.