When working with Oracle databases, it's common to face scenarios where you need to retrieve only a subset of rows from your data set, especially after sorting the data in a specific order. Limiting the number of rows returned by a query can be crucial for performance, especially when dealing with large volumes of data. There are several strategies in Oracle SQL to achieve this, primarily using the ROWNUM pseudocolumn, the FETCH clause, and subqueries. Each method has its advantages and appropriate use cases.

Using ROWNUM to Limit Rows

In Oracle, ROWNUM is a pseudocolumn that assigns a unique, sequential number to each row as it is retrieved from the database. You can use ROWNUM to limit the number of rows returned. However, because ROWNUM is applied before sorting (ORDER BY), you must use a subquery if you want to sort the results first and then apply the row limit.

1SELECT *
2FROM (
3  SELECT column1, column2
4  FROM table_name
5  ORDER BY column1 ASC
6)
7WHERE ROWNUM <= 10;

In this example, the inner query sorts the table by column1, and then the outer query limits the results to the first 10 rows.

Using FETCH FIRST/NEXT

Oracle 12c introduced the FETCH FIRST clause, providing a more straightforward and SQL-standard way to limit the results of a query. This method is particularly readable and avoids the need for subqueries used with ROWNUM.

1SELECT column1, column2
2FROM table_name
3ORDER BY column1 ASC
4FETCH FIRST 10 ROWS ONLY;

You can also use FETCH NEXT which is functionally equivalent to FETCH FIRST:

1SELECT column1, column2
2FROM table_name
3ORDER BY column1 ASC
4FETCH NEXT 10 ROWS ONLY;

Using the OFFSET Clause

Sometimes, in addition to limiting the number of rows, you might want to skip a certain amount of rows before starting to return the rows (similar to SQL pagination). This can be done using the OFFSET clause along with FETCH.

1SELECT column1, column2
2FROM table_name
3ORDER BY column1
4OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

This query will skip the first 5 rows and then fetch the next 5 rows from the result set.

Comparing Methods

Performance Considerations

It's vital to consider performance when limiting query results. Using ROWNUM on an ordered subquery might be less efficient than using ORDER BY with FETCH, because the database might be able to optimize the retrieval strategy better with newer clauses like FETCH. Always check the execution plan for your queries to ensure optimal performance.

Conclusion

Oracle provides various methods for limiting the number of rows returned by a query, and choosing the right method depends on the specific requirements of your application and the version of Oracle you are using. Modern approaches like FETCH are generally recommended for their readability and alignment with SQL standards, but in some environments, using ROWNUM might still be necessary.

Understanding these techniques and their trade-offs empowers you to write more efficient and effective SQL queries, enhancing both performance and maintainability of your database operations.