How to Limit Rows with Offset in Oracle SQL: Techniques and Examples

Introduction

In many database applications, retrieving a specific subset of data is crucial. Whether for pagination, performance optimization, or simply limiting results, knowing how to limit the number of rows returned by an SQL query is essential. In MySQL, this is straightforward with the LIMIT clause. However, Oracle SQL requires different techniques, especially before version 12c, and even newer methods in later versions.

This tutorial explores various methods for achieving similar functionality in Oracle: limiting the number of rows after ordering, including setting offsets to skip a certain number of rows before retrieving data.

Limiting Rows with ROWNUM

Before Oracle 12c, one common approach involved using ROWNUM, an Oracle-specific pseudocolumn. However, this method required careful handling as ROWNUM assigns numbers to rows before sorting:

SELECT * 
FROM (
    SELECT s.*, ROWNUM AS rn 
    FROM sometable s 
    ORDER BY name
) 
WHERE rn BETWEEN 21 AND 30;

In the example above, we first assign row numbers in a subquery and then filter based on desired row numbers. This approach ensures sorting occurs before applying limits.

Performance Considerations

Performance is critical when dealing with large datasets. Two common methods include:

AskTom Method: Using nested queries to simulate LIMIT functionality.

SELECT * 
FROM (
    SELECT a.*, ROWNUM rnum 
    FROM (
        SELECT * 
        FROM sometable 
        ORDER BY name
    ) a 
    WHERE ROWNUM <= 30
)
WHERE rnum >= 21;

Analytical Method: Utilizing analytic functions like ROW_NUMBER().

SELECT * 
FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY name) MyRow 
    FROM sometable t
) 
WHERE MyRow BETWEEN 21 AND 30;

Both methods have their trade-offs in terms of performance, especially on large datasets.

Oracle 12c and Later: OFFSET-FETCH Clause

Starting with Oracle 12c (version 12.1), the OFFSET and FETCH NEXT clauses provide a more standardized approach to limiting results:

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

This syntax directly supports pagination, making it easier to retrieve specific subsets of data.

Advanced Features

Ties in LIMIT: Oracle allows fetching rows with ties using FETCH FIRST ... WITH TIES:
```
SELECT * 
FROM sometable 
ORDER BY score DESC 
FETCH FIRST 5 ROWS WITH TIES;
```
Percentage-Based Limits: Fetch a percentage of the dataset, useful for sampling:
```
SELECT * 
FROM sometable 
ORDER BY name 
FETCH FIRST 20 PERCENT ROWS ONLY;
```

Conclusion

Limiting rows in Oracle SQL involves various techniques depending on the version and specific requirements. Pre-12c versions require creative use of ROWNUM and nested queries, while post-12c introduces more intuitive syntax with OFFSET-FETCH, aligning closer to other SQL dialects like MySQL.

Understanding these methods allows for efficient data retrieval tailored to application needs, enhancing performance and user experience in database-driven applications.