What is the ADF View Object Range Paging Feature?

The ADF Business Components view object component supports an access mode called RANGE_PAGING. The feature allows your applications to page back and forth through data, a range at a time, in a more efficient way for large data sets.

It leverages a feature called a "Top-N" query that the Oracle database can do to efficiently return the first N ordered rows in some query. For example, you might have a query like:

SELECT EMPNO, ENAME,SAL FROM EMP ORDER BY SAL DESC

If you want to retrieve the top 5 employees by salary, you can write a query like:

SELECT * FROM (
   SELECT X.*,ROWNUM AS RN FROM (
      SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC 
   ) X
) WHERE RN <= 5

which gives you results like:

     EMPNO ENAME           SAL         RN
---------- -------- ---------- ----------
      7839 KING           5000          1
      7788 SCOTT          3000          2
      7902 FORD           3000          3
      7566 JONES          2975          4
      7698 BLAKE          2850          5

If you start fiddling with the range of the rownum parameter that you include in the outermost WHERE clause predicate, then you can ask the database to retrieve for you the rows in this ordered list of say, 6 through 10 like this:

SELECT * FROM (
  SELECT X.*,ROWNUM AS RN FROM  (
     SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC
  ) X
) WHERE RN BETWEEN 6 AND 10

This means that if you want to show R rows per page, and currently want to look at page P of that result set, then you would write a query like:

SELECT * FROM (
  SELECT X.*,ROWNUM AS RN FROM (
    SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC
  ) X
) WHERE RN BETWEEN ((:P - 1) * :R) + 1 AND (:P) * :R

As your result set gets more and more rows, it gets more and more efficient to use this technique to page through the rows because in general, rather than bringing hundreds of rows back from the database (across SQL*Net in particular), instead you can "push" the request for "Page P" of the R-rows-per-page down into the database so that the database only returns a single "page" of rows across the network. Never more than a handful of rows at a time.

While you of course can use our Expert Mode SQL to enter a clever query like the above in, and you can put bind variables in it, and you can calculate the values of those bind variables yourself based on the current page you want to show the user and the current range size, you can simplify your life using the ADF view object.

When an ADF view object's access mode is set to RANGE_PAGING, the VO takes a query like:

SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC

and automatically "wraps" it with the extra outer SQL in a way similar to the above example, and automatically calculates the values of the bind variables (and sets them for you) so that you fetch back the rows of the current pageful from the database, based on the current range size.

When a view object operates in RANGE_PAGING access mode, it only keeps the current range (or "page") of rows in memory in the view row cache at a time. That is, if you are paging through results ten at a time, then on the first page, we'll have in memory rows 1 through 10. When you navigate to page two, we'll have rows 11 through 20 in the cache. This also can help make sure for large rowsets that you don't end up with tons of rows cached just because you want to preserve the ability to scroll backwards and forwards.

You might ask yourself, "Why wouldn't I always want to use RANGE_PAGING mode?" The answer is that using range paging potentially causes more overall queries to be executed as you are navigating forward and backward through your view object rows. Situations when you would not want to use RANGE_PAGING mode are:

• You plan to read all the rows in the rowset immediately (for example, to populate a drop-down list).

  In this case your Range Size would be set to -1 and there really is only a single "page" of all rows, so range paging does not add value.

• You need to page back and forth through a small-sized rowset.

  If you have 30-40 rows or fewer, and are paging through them 10 at a time, with RANGE_PAGING mode you will execute a query each time you go forward and backward to a new page. In normal mode, you will cache the view object rows as you read them in and paging backwards through the previous pages will not re-execute queries to show those already-seen rows.

In the case of a very large (or unpredictably large) row set, the trade off of potentially doing a few more queries — each of which only returns up to RangeSize rows from the database — is more efficient then trying to cache all of the previously-viewed rows. This is especially true if you allow the user to jump to an arbitrary page in the list of results. Doing so in normal mode requires fetching and caching all of the rows between the current page and the page the users jumps to. In RANGE_PAGING mode, it will ask the database just for the rows on that page. Then, if the user jumps back to a page of rows that they have already visited, in RANGE_PAGING mode, those rows get requeried again since only the current page of rows is held in memory in this mode.