Fetch First Rows feature in Oracle

FETCH FIRST feature is known as the "ROWNUM" pseudocolumn or the "FETCH FIRST n ROWS ONLY" clause. It allows you to limit the number of rows returned in a query result. Here's how you can use it:

Using ROWNUM pseudocolumn:
SELECT *
FROM table_name
WHERE ROWNUM <= n;

In this example, replace table_name with the name of the table you're querying, and n with the desired number of rows you want to fetch.

Note: When using the ROWNUM pseudocolumn, it's essential to apply the condition on ROWNUM after any necessary ordering or filtering, as ROWNUM is assigned to rows before ordering is applied.

Using FETCH FIRST n ROWS ONLY clause (starting from Oracle 12c):
SELECT *
FROM table_name
ORDER BY column_name
FETCH FIRST n ROWS ONLY;

In this example, replace table_name with the name of the table you're querying, column_name with the column you want to order by, and n with the desired number of rows you want to fetch.

The FETCH FIRST clause allows you to specify an ORDER BY clause, ensuring consistent results when retrieving a limited number of rows.

Performance benefits of using the "FETCH FIRST":
Reduced Data Transfer: By limiting the number of rows fetched from the database, the "FETCH FIRST" feature reduces the amount of data transferred between the database server and the client application. This can significantly improve query performance, especially when dealing with large result sets.
 
Query Optimization: The "FETCH FIRST" feature allows the Oracle optimizer to optimize the query execution plan based on the specified row limit. It enables the optimizer to choose more efficient access paths, such as using an index scan or a fast full scan, instead of performing a full table scan when only a limited number of rows are required.
 
Faster Response Time: By fetching only the necessary rows, the "FETCH FIRST" feature can reduce the time it takes to process and return query results. This can lead to faster response times for queries, improving the overall user experience.
 
Efficient Pagination: When implementing pagination functionality, the "FETCH FIRST" feature can be particularly useful. It allows you to retrieve specific pages of data efficiently by combining the "FETCH FIRST" clause with appropriate offset values. This can enhance performance when displaying paginated results in applications.
 
Improved Scalability: By limiting the number of rows fetched, the "FETCH FIRST" feature helps in optimizing resource utilization. It reduces memory consumption and can improve the scalability of the application by efficiently handling large result sets.