Oracle SQL allows us to manipulate and manage data in relational databases. Common tasks include querying data, creating tables, and developing complex data processing routines. One frequent task that developers accomplish with SQL is selecting particular rows from a database table. Sometimes, we might need to limit how many rows we’re selecting, often for performance reasons. By default, when you write a “SELECT” statement in Oracle SQL, it retrieves all the rows from the designated table that meet your criteria. But what if we only want the first 10 rows? In this guide, we are going to demonstrate how to select only the first 10 rows in Oracle SQL.
SELECT *
FROM (SELECT *
FROM your_table
ORDER BY some_column)
WHERE ROWNUM <= 10;
[/code]
Oracle SQL uses a special pseudo-column called “ROWNUM” to allow developers to perform operations such as these. This pseudo-column gives us the number of each row when it gets retrieved, starting from 1 and going up to however many rows there are in the query.
Detailed code explanation
Let’s break down what this query means.
[code lang=”Oracle SQL”]
SELECT * FROM (SELECT * FROM your_table ORDER BY some_column)
This first part of the query selects all the columns from your table and sorts them by “some_column”. We are sorting it because ROWNUM is assigned to the rows after the order by clause and before any filtering takes place.
WHERE ROWNUM <= 10; [/code] The second part of the query is where the "ROWNUM" pseudo-column comes into play. It basically tells Oracle to only give us the rows where the ROWNUM is 10 or less, hence, the first 10 rows. One key note here is that ROWNUM values are assigned to the retrieved rows starting from 1 and the values do not get committed until after the "ORDER BY" clause is fully processed.
ROWNUM and Performance
Using ROWNUM can greatly improve performance, especially when dealing with large amounts of data. Oracle doesn’t have to fetch all the data from the disk and hold them into memory before it starts returning the results. It can return the results as soon as it gets the first 10 that fulfills all the conditions in the SQL command.
Similar Functions
A similar function to ROWNUM in Oracle SQL is FETCH FIRST n ROWS ONLY. However, this clause is available only in Oracle 12c and later versions.
[code lang=”Oracle SQL”]
SELECT *
FROM your_table
ORDER BY some_column
FETCH FIRST 10 ROWS ONLY;
Overall, Oracle SQL provides programmers with several useful techniques to limit the number of rows returned by a SELECT query.
