Notepad:Oracle Pagination

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 PSEUDO-COLUMN field called ROWNUM that can assist in pagination or retrieved data. Unfortunately, this field is set as data is retrieved from a query, BEFORE any ordering. In order to work around this limitation, we need to encapsulate the query in 2 sub-selects:

Original Query

 SELECT u.* 
   FROM "User" u 
   WHERE emailaddress LIKE '%foldera%'
   order by lname, fname

Optimized Query

From : On ROWNUM and Limiting Results

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;

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

Unoptimized Query

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;

Generalized Form

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

Specialized Form

If the original query DOES NOT contain any sorting or ordering, we can skip one level of inner subselect:

SELECT * 
   FROM ( 
      SELECT ROWNUM AS display_rows, u.* 
      FROM "User" u 
      WHERE emailaddress LIKE '%foldera%'
   ) inside 
WHERE display_rows >= 5 AND display_rows <= 10;

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 RECORDS_PER_PAGE info (and have it recalculated by an onresize="recalcsize();" construct and have AJAX calls pass this info to the calling GET... 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 !