FETCH Clause

The FETCH clause in SQL is commonly used to limit the number of rows returned by a query, and it is frequently used with the OFFSET clause to provide a way to paginate query results. In Oracle, this feature was introduced in Oracle 12c (12.1), so it is not available in earlier versions of Oracle.

General Syntax of the FETCH Clause

The FETCH clause is usually combined with the OFFSET clause to provide a more readable and efficient way to implement pagination in queries. The syntax is as follows:

SELECT column1, column2, ...

FROM table_name

ORDER BY column_name

OFFSET n ROWS FETCH NEXT m ROWS ONLY;

·        OFFSET n ROWS: Skips the first n rows. If n = 0, no rows are skipped, and the first row will be returned.

·        FETCH NEXT m ROWS ONLY: Returns the next m rows from the result set, starting after the rows skipped by the OFFSET.

Detailed Explanation

1. Usage with ORDER BY

The FETCH clause is often used with an ORDER BY clause. This makes sense because the rows need to be ordered in some predictable way to ensure consistent results for pagination.

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY last_name

OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

In this example:

·        OFFSET 5 ROWS skips the first 5 rows based on the ordering (in this case, alphabetically by last_name).

·        FETCH NEXT 10 ROWS ONLY then retrieves the next 10 rows from the ordered result set.

This would be useful for implementing pagination in an application, where you need to show a page of results at a time.

2. FETCH FIRST vs. FETCH NEXT

·        FETCH FIRST: This syntax retrieves the first m rows.

·        SELECT * FROM employees ORDER BY hire_date FETCH FIRST 10 ROWS ONLY;

·        FETCH NEXT: This is used for pagination to fetch the next m rows, usually after an OFFSET has been applied.

·        SELECT * FROM employees ORDER BY hire_date OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Both FIRST and NEXT are interchangeable in some contexts, but NEXT is often more useful in paginated results.

3. Skipping n Rows Using OFFSET

The OFFSET clause determines how many rows to skip before fetching the next set of rows. It's very useful for paginating data.

For example, if you are creating a pagination system where you want to display 20 records per page, OFFSET and FETCH would work together like this:

·        Page 1: OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

·        Page 2: OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

·        Page 3: OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY

And so on.

4. Performance Considerations

While FETCH and OFFSET make pagination easier, performance can become a concern when dealing with large datasets. Oracle processes the entire query first and then applies the OFFSET and FETCH clauses, meaning that even if you're only interested in the last 10 records, Oracle might still have to scan through the entire table.

To improve performance:

·        Indexes: Ensure that you have appropriate indexes on the columns used in the ORDER BY clause.

·        Smaller Result Sets: When possible, try to limit the dataset as early as possible in the query (e.g., by filtering rows in the WHERE clause before applying OFFSET and FETCH).

5. FETCH with ALL

You can also use FETCH ALL to retrieve all rows without specifying a limit:

SELECT * FROM employees ORDER BY hire_date FETCH ALL ROWS ONLY;

This behaves like a query without the FETCH clause, meaning that all rows will be returned.

Example: Pagination in Practice

Let’s say you want to paginate through a list of employees, showing 10 records per page. You would use something like:

·        Page 1: OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

·        Page 2: OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

·        Page 3: OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

For each page, you would update the OFFSET based on the page number to get the correct slice of data.

Key Points to Remember

1.     Introduced in Oracle 12c: The FETCH and OFFSET clauses were introduced in Oracle 12c (12.1), so they are not available in older versions of Oracle.

2.     Order is important: The ORDER BY clause is mandatory if you want to use OFFSET and FETCH in a meaningful way, as it determines the sequence of rows returned.

3.     Efficient Pagination: While FETCH and OFFSET make pagination simple, you should be aware of potential performance impacts when working with large datasets.

4.     Pagination Queries: The FETCH clause is very helpful for implementing pagination in applications or reports.

Example with OFFSET and FETCH for Pagination

Here’s a full example showing how you can paginate data:

SELECT employee_id, first_name, last_name, hire_date

FROM employees

ORDER BY hire_date

OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

In this case:

·        OFFSET 20 ROWS skips the first 20 rows.

·        FETCH NEXT 10 ROWS ONLY retrieves the next 10 rows.

Conclusion

The FETCH clause in Oracle provides an efficient and readable way to limit query results, particularly when combined with OFFSET for pagination. By understanding how to use these clauses properly, you can efficiently manage large datasets and implement pagination for your applications.