67.52.184.2: Oracle Pagination

2007-06-25T23:30:44Z

Oracle Pagination

New page

== The Problem ==
We need to be able to paginate result sets without burdening the servers with too many data and unnecessary sorts. It is also best to order the data straight from the database, which is the natural and best tool for the job.

== The Solution ==
Oracle has a <code>PSEUDO-COLUMN</code> field called <code>ROWNUM</code> that can assist in pagination or retrieved data. Unfortunately, this field is set as data is retrieved from a query, <em>BEFORE</em> any ordering. In order to work around this limitation, we need to encapsulate the query in 2 sub-selects:

=== Original Query ===
<pre>
SELECT u.*
FROM "User" u
WHERE emailaddress LIKE '%foldera%'
order by lname, fname
</pre>

=== Optimized Query ===
From : [http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html On ROWNUM and Limiting Results]

<pre>
SELECT *
FROM ( SELECT /*+ FIRST_ROWS(n) */
ROWNUM AS display_rows, inside.*
FROM (
SELECT u.*
FROM "User" u
WHERE emailaddress LIKE '%foldera%'
ORDER BY lname, fname
) inside
WHERE ROWNUM <= 10
) outside
WHERE display_rows >= 5;
</pre>

This query will only get the first <code>LAST_RECORD</code> records and save a lot of time and memory for the first pages of a query. Note that <code>FIRST_RECORD</code> and <code>LAST_RECORD</code> are reversed.

=== Unoptimized Query ===
<pre>
SELECT *
FROM (
SELECT ROWNUM AS display_rows,
inside.*
FROM (
SELECT u.*
FROM "User" u
WHERE emailaddress LIKE '%foldera%'
ORDER BY lname, fname
) inside
) outside
WHERE display_rows >= 5 AND display_rows <= 10;
</pre>

=== Generalized Form ===
<pre>
SELECT *
FROM (
SELECT /*+ FIRST_ROWS(n) */
ROWNUM AS display_rows, inside.*
FROM (
<... original query with ordering here ...>
) inside
WHERE ROWNUM <= :LAST_RECORD
) outside
WHERE display_rows >= :FIRST_RECORD
</pre>

=== Specialized Form ===
If the original query DOES NOT contain any sorting or ordering, we can skip one level of inner subselect:
<pre>
SELECT *
FROM (
SELECT ROWNUM AS display_rows, u.*
FROM "User" u
WHERE emailaddress LIKE '%foldera%'
) inside
WHERE display_rows >= 5 AND display_rows <= 10;
</pre>

Unfortunately, this requires adding a field to the original inner query.

== Strategy ==
The strategy is then have a all paged queries wrapped by the proper construct. Since all ou queries are now external strings, this is a trivial task.

The first algorithm that comes to mind is:
FIRST_RECORD := (PAGE_NO - 1) * RECORDS_PER_PAGE + 1;
LAST_RECORD := PAGE_NO * RECORDS_PER_PAGE;

It is a good idea to have Javascript determine the <code>RECORDS_PER_PAGE</code> info (and have it recalculated by an <code>onresize="recalcsize();"</code> construct and have AJAX calls pass this info to the calling <code>GET...</code> routine

== Discussion ==
* Does this get the full data set and simply filters what we need
yes
* Isn't this wasteful?
The data has to be fully analyzed to get this. Whether on the DB or web server or client machine. The best place for this is on the server where we get the most optimized engine for manipulating sorted data.
* Can we generalize the sorting without rewriting the query?
I'll have to look at it more ... keep in touch !

Notepad:Oracle Pagination - Revision history

67.52.184.2: Oracle Pagination