Row limiting is a powerful and common feature that is implemented by most relational database engines. Row limiting refers to the number of rows that an SQL SELECT statement can return.
In most cases, the maximum number of limits is set by the database engine. However, we can override this value in our query using various techniques. Before changing the number of rows that are returned by an SQL SELECT statement, it is good to consider the available resources in your system.
The row limit is a valuable feature for several reasons. First, it helps to prevent the queries from returning an excessive amount of data, which can use up the valuable resources and cause some performance issues.
Second, it can help prevent the SQL injection attacks by limiting the amount of data that an attacker can extract from the database.
This post will discuss a short format for limiting the number of records that are returned from a given Oracle query. Remember that there is no LIMIT clause in the Oracle database as you will find in other databases such as MySQL, PostgreSQL, etc.
Oracle ROWNUM
For us to implement a limit-like clause, we use the rownum function in SQL. This function returns the number of rows in a given result.
Example Usage:
Suppose we have a table which contains the database information as shown in the following image:
Problem:
Suppose we are only interested in the first five rows from the table.
Solution:
We can use a query as shown in the following snippet:
In the previous query, we define a conditional statement which ensures that the returned number of rows is less than or equal to 5. This forces the number of records from the query to stop at five.
An example output is as follows:
Conclusion
The Oracle ROWNUM function is a powerful tool to limit the number of rows that are returned in an Oracle SQL query. It can be used in various ways to control the number of rows that are returned by a query and ensures that only the most relevant data is returned.
