ROWNUM in Oracle: How to Achieve Proper Pagination and Avoid Common Pitfalls?

Oracle ROWNUM and Pagination: Common Issues and Solutions

This article addresses frequent challenges encountered when using ROWNUM for pagination in Oracle SQL.

Problem 1: Ineffective WHERE Clause with ROWNUM

Why does SELECT * FROM Person WHERE rownum > 100 return no results?

ROWNUM is assigned after the initial filtering (predicate) phase. Because a ROWNUM value is only incremented after assignment, a value greater than 1 cannot be directly selected using a WHERE clause in this manner. No rows satisfy the condition ROWNUM > 100.

Problem 2: ROWNUM BETWEEN Limitations

Why is WHERE rownum BETWEEN lowerBound AND upperBound not suitable for pagination?

This syntax is also ineffective because ROWNUM assignment precedes the WHERE clause evaluation. Oracle cannot directly select a range of ROWNUM values.

Solution: Oracle 12c and Beyond (Top-n Row Limiting)

Oracle 12c introduced a superior approach: "Top-n Row Limiting" using OFFSET and FETCH. For example:

SELECT empno, sal
FROM   emp
ORDER BY sal
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

This efficiently retrieves rows 5-8 (offset 4, fetch 4). This method is preferred over techniques relying solely on ROWNUM.

Problem 3: Hiding the ROWNUM Column

How can we prevent the ROWNUM column from appearing in the result set?

Simply list only the desired columns in the outer SELECT statement. Alternatively, in SQL*Plus, you can use the NOPRINT command to suppress specific columns' output.

Problem 4: Ensuring Correct Pagination with ROWNUM (Older Oracle Versions)

Can ROWNUM achieve correct pagination?

Yes, but it requires a nested query structure:

SELECT val
FROM   (SELECT val, rownum AS rnum
        FROM   (SELECT val
                FROM   t
                ORDER BY val)
        WHERE rownum <= 8)
WHERE  rnum >= 5;

This correctly retrieves rows 5-8. The inner query assigns ROWNUM, the middle query filters based on the upper bound, and the outer query filters based on the lower bound. However, the OFFSET/FETCH method remains the more efficient and readable option in Oracle 12c and later.

The above is the detailed content of ROWNUM in Oracle: How to Achieve Proper Pagination and Avoid Common Pitfalls?. For more information, please follow other related articles on the PHP Chinese website!