Sorting Paginated Results in Oracle SQL

Introduction

As a developer working with large datasets and complex queries, pagination is an essential technique for improving performance, scalability, and user experience. In this article, we’ll delve into the world of paginated results in Oracle SQL, exploring common challenges and providing practical solutions to overcome them.

Datatables Server-Side Pagination

The problem statement revolves around implementing datatables server-side pagination with a custom query builder. The provided code snippet demonstrates how to construct a paginated query using Oracle’s ROWNUM pseudocolumn. However, we’ll soon discover that this approach has limitations when it comes to sorting results.

Understanding the Challenge

Let’s break down the given query:

public static String buildPaginatedQueryForOracle(String baseQuery, PaginationCriteria paginationCriteria) {
    // ...
}

This method takes a baseQuery and a paginationCriteria object as input. The query builder uses Oracle’s ROWNUM pseudocolumn to fetch paginated results. However, when sorting by CNT in the ORDER BY clause, we encounter an unexpected behavior.

Sorting Results

The query is executed first with a default sort order (by ID_LIST DESC). Then, only the top 5 rows are selected based on this initial ordering. Finally, the remaining results are sorted by CNT in descending order using the following query:

SELECT *
FROM (
    SELECT FILTERED_ORDERED_RESULTS.*, COUNT(1) OVER() TOTAL_RECORDS
    FROM (
        SELECT BASEINFO.*,
               ROWNUM AS RN
          FROM (
               SELECT A.ID_LIST            AS ID,
                      A.NAME,
                      A.DATE_CREATE        AS DATECREATE,
                      A.DATE_UPDATE        AS DATEUPDATE,
                      A.USER_ID            AS USERID,
                      A.TYPE,
                      NVL(
                            B.CNT, 0
                        )        CNT
                 FROM MAP_S_LIST_ARTS                                                           A
                 LEFT JOIN (
                             SELECT ID_LIST,
                                    COUNT(*) CNT
                              FROM MAP_LIST_ARTS
                             GROUP BY ID_LIST
                     )         B ON A.ID_LIST = B.ID_LIST
                  ORDER BY A.ID_LIST DESC
              ) BASEINFO
    ) FILTERED_ORDERED_RESULTS
    ORDER BY CNT DESC
)
WHERE RN > (:PAGE * 5) AND RN <= (:PAGE + 1) * 5

As we can see, the query is sorted by CNT after selecting only the top 5 rows. This behavior may seem counterintuitive and can lead to unexpected results.

Correct Approach

To correctly sort paginated results in Oracle SQL, we need to rethink our approach. Instead of using ROWNUM, we should rely on Oracle’s built-in pagination features, such as FETCH FIRST and FETCH NEXT.

Here’s an updated implementation:

public static String buildPaginatedQueryForOracle(String baseQuery, PaginationCriteria paginationCriteria) {
    // ...
}

// Example query using FETCH FIRST and FETCH NEXT
SELECT *
FROM (
    SELECT FILTERED_ORDERED_RESULTS.*, COUNT(1) OVER() TOTAL_RECORDS
    FROM (
        SELECT BASEINFO.*,
               ROWNUM AS RN
          FROM (
               SELECT A.ID_LIST            AS ID,
                      A.NAME,
                      A.DATE_CREATE        AS DATECREATE,
                      A.DATE_UPDATE        AS DATEUPDATE,
                      A.USER_ID            AS USERID,
                      A.TYPE,
                      NVL(
                            B.CNT, 0
                        )        CNT
                 FROM MAP_S_LIST_ARTS                                                           A
                 LEFT JOIN (
                             SELECT ID_LIST,
                                    COUNT(*) CNT
                              FROM MAP_LIST_ARTS
                             GROUP BY ID_LIST
                     )         B ON A.ID_LIST = B.ID_LIST
                  ORDER BY A.ID_LIST DESC
              ) BASEINFO
    ) FILTERED_ORDERED_RESULTS
    FETCH FIRST #PAGE_SIZE# ROWS ONLY
    FETCH NEXT #PAGE_SIZE# ROWS OVER (ORDER BY CNT DESC)
)
WHERE RN BETWEEN 1 AND #PAGE_NUMBER# * #PAGE_SIZE#

In this revised implementation, we use FETCH FIRST to select the first page of results and FETCH NEXT to fetch subsequent pages. The ORDER BY clause ensures that results are sorted by CNT in descending order.

Conclusion

Sorting paginated results in Oracle SQL can be a challenging task, especially when relying on third-party libraries or custom query builders. However, by leveraging Oracle’s built-in pagination features and understanding the limitations of alternative approaches, we can develop efficient and scalable solutions for our needs.

Last modified on 2024-09-06