Oracle Pagination: Complete Guide to LIMIT Clause

Oracle doesn’t have a built-in LIMIT clause like MySQL, but there are several effective approaches to implement pagination that work correctly with ORDER BY and support both limit and offset functionality. The most modern and recommended method for Oracle 12c and later versions uses the FETCH FIRST clause, while earlier versions require using analytical functions or more complex workarounds.

Contents

• Oracle 12c+ FETCH FIRST Syntax
• Row_Number() Analytical Function Approach
• ROWNUM Workarounds for Older Versions
• Performance Considerations
• Complete Pagination Examples

Oracle 12c+ FETCH FIRST Syntax

Starting with Oracle Database 12c, Oracle introduced standardized SQL pagination syntax that works exactly like MySQL’s LIMIT clause. This is the recommended approach for modern Oracle databases:

SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

This query:

• Skips the first 20 rows (OFFSET 20 ROWS)
• Returns the next 10 rows (FETCH NEXT 10 ROWS ONLY)
• Respects the ORDER BY clause for proper pagination

You can also use FIRST_ROWS for optimization:

SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
FIRST_ROWS(10);

The FETCH FIRST clause supports various options:

• FETCH FIRST n ROWS ONLY - equivalent to LIMIT n
• OFFSET m ROWS FETCH NEXT n ROWS ONLY - equivalent to LIMIT m, n
• PERCENT option for percentage-based pagination

Row_Number() Analytical Function Approach

For Oracle versions before 12c, the most reliable method is using the ROW_NUMBER() analytical function. This approach correctly handles the ORDER BY clause before applying pagination:

SELECT *
FROM (
    SELECT 
        a.*, 
        ROW_NUMBER() OVER (ORDER BY name) as rn
    FROM sometable a
)
WHERE rn > 20 AND rn <= 30;

To get the 21st to 30th rows (equivalent to MySQL’s LIMIT 20, 10):

SELECT *
FROM (
    SELECT 
        a.*, 
        ROW_NUMBER() OVER (ORDER BY name) as rn
    FROM sometable a
)
WHERE rn BETWEEN 21 AND 30;

You can also use this method with bind variables for better performance:

SELECT *
FROM (
    SELECT 
        a.*, 
        ROW_NUMBER() OVER (ORDER BY name) as rn
    FROM sometable a
)
WHERE rn > :offset AND rn <= :offset + :limit;

ROWNUM Workarounds for Older Versions

While ROWNUM is evaluated before ORDER BY, you can create a workaround using subqueries. The key is to apply the ORDER BY in an inner query and then filter with ROWNUM in an outer query:

SELECT *
FROM (
    SELECT a.*
    FROM sometable a
    ORDER BY name
)
WHERE ROWNUM <= 30
MINUS
SELECT *
FROM (
    SELECT a.*
    FROM sometable a
    ORDER BY name
)
WHERE ROWNUM <= 20;

This approach:

• First orders all rows by name in the inner query
• Then gets the first 30 rows (to include our target range)
• Subtracts the first 20 rows to leave rows 21-30

However, this method can be inefficient for large datasets as it processes the entire result set.

Performance Considerations

When implementing pagination in Oracle, consider these performance factors:

1. Indexing: Ensure the ORDER BY columns are properly indexed to avoid full table scans

2. Bind Variables: Use bind variables (:offset, :limit) instead of hard-coded values for cursor reuse

3. Pagination Depth: Avoid deep pagination (e.g., page 1000 with 10 rows per page) as Oracle must process all previous rows

4. Result Set Size: For large tables, consider adding a WHERE clause to limit the initial result set before pagination

5. Query Hints: Use /*+ FIRST_ROWS(n) */ hint to optimize for pagination queries

Complete Pagination Examples

Here are complete examples showing different pagination approaches:

Method 1: Oracle 12c+ (Recommended)

-- Page 3 with 10 rows per page (rows 21-30)
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Method 2: Row_Number() (Pre-12c)

-- Page 3 with 10 rows per page (rows 21-30)
SELECT *
FROM (
    SELECT 
        a.*, 
        ROW_NUMBER() OVER (ORDER BY name) as rn
    FROM sometable a
)
WHERE rn BETWEEN 21 AND 30;

Method 3: ROWNUM with MINUS (Legacy)

-- Page 3 with 10 rows per page (rows 21-30)
SELECT *
FROM (
    SELECT a.*
    FROM sometable a
    ORDER BY name
)
WHERE ROWNUM <= 30
MINUS
SELECT *
FROM (
    SELECT a.*
    FROM sometable a
    ORDER BY name
)
WHERE ROWNUM <= 20;

Method 4: With Dynamic SQL

-- Using dynamic SQL for variable pagination
BEGIN
    EXECUTE IMMEDIATE 
        'SELECT * FROM sometable ORDER BY name OFFSET :1 ROWS FETCH NEXT :2 ROWS ONLY'
    USING 20, 10;
END;

For most modern applications using Oracle 12c or later, the FETCH FIRST syntax provides the cleanest, most efficient solution that closely matches MySQL’s LIMIT functionality. For legacy systems, the ROW_NUMBER() approach offers the best balance of performance and readability.

Conclusion

• Oracle 12c+ provides native pagination with OFFSET...FETCH FIRST syntax that works exactly like MySQL’s LIMIT clause
• For pre-12c databases, use ROW_NUMBER() analytical function for proper pagination with ORDER BY
• Avoid simple ROWNUM filtering as it evaluates before ordering, causing incorrect results
• Consider performance implications when implementing pagination, especially for large datasets
• Use bind variables and proper indexing to ensure efficient pagination queries

The FETCH FIRST approach is now the standard and recommended method for Oracle pagination when using compatible database versions.