Efficient Pagination Techniques for APEX Reports

The Pagination Performance Problem

When a report queries a table with millions of rows, pagination becomes a performance concern. APEX’s built-in pagination works well for moderate data sets, but large tables require careful optimization to keep page load times acceptable. The key insight is that pagination involves two expensive operations: counting the total rows and fetching the current page’s rows.

Avoiding the Total Row Count

The most impactful optimization is eliminating the total row count. Displaying “Showing 1-25 of 3,847,291 rows” requires a full COUNT(*) query that can take seconds on a large table. APEX offers two alternatives:

Set the “Maximum Rows to Process” to a reasonable ceiling like 10,000 and set “When More Than Max Rows” to display “Rows 1-25 of more than 10,000.” This caps the count query at 10,000 rather than counting all rows. Even better, for Interactive Grids, disable the total row count entirely through the JavaScript initialization code as described in the lazy loading article.

Row Limiting Clause (12c and Later)

Oracle 12c introduced the ANSI-standard row limiting clause which is the most efficient way to implement pagination in SQL:

SELECT employee_id, first_name, last_name, salary
  FROM employees
 WHERE department_id = :P10_DEPT_ID
 ORDER BY last_name
 OFFSET :P10_PAGE_OFFSET ROWS
 FETCH NEXT :P10_PAGE_SIZE ROWS ONLY;

This is cleaner and generally more efficient than the traditional ROWNUM approach. The optimizer can use this information to stop fetching rows once the page is filled.

Keyset Pagination for Large Datasets

For tables with tens of millions of rows, traditional OFFSET-based pagination degrades as the user pages further in because the database must skip over all preceding rows. Keyset pagination uses the last row’s sort key to fetch the next page:

-- First page
SELECT employee_id, last_name, salary
  FROM employees
 WHERE department_id = :P10_DEPT_ID
 ORDER BY last_name, employee_id
 FETCH FIRST 25 ROWS ONLY;

-- Next page: use the last row's values as the starting point
SELECT employee_id, last_name, salary
  FROM employees
 WHERE department_id = :P10_DEPT_ID
   AND (last_name, employee_id) > (:P10_LAST_NAME, :P10_LAST_ID)
 ORDER BY last_name, employee_id
 FETCH FIRST 25 ROWS ONLY;

This approach is O(1) for any page number because it uses an index seek instead of skipping rows. The trade-off is that you cannot jump to arbitrary page numbers; you can only go forward and backward.

Indexing for Pagination

Whatever pagination approach you use, ensure there is a composite index that covers the ORDER BY columns and the WHERE clause columns. Without this index, every page request requires a full table sort, which defeats the purpose of pagination.