OFFSET clause

The OFFSET clause in Oracle SQL is used in conjunction with the FETCH clause to control which rows to return from a result set. The OFFSET clause is especially useful for pagination scenarios, where you need to retrieve a specific subset of rows from a larger result set.

It was introduced in Oracle 12c (12.1) and provides a more readable and standardized way to implement pagination compared to older techniques like ROWNUM or ROW_NUMBER().

Here’s a detailed breakdown of how the OFFSET clause works:

1. Basic Syntax

The basic syntax of the OFFSET clause, when used with FETCH, 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 in the result set.

·        FETCH NEXT m ROWS ONLY: Retrieves the next m rows after the n rows have been skipped.

Example:

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY last_name

OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

This query:

·        Skips the first 10 rows (based on the ORDER BY last_name).

·        Fetches the next 5 rows.

2. Usage of OFFSET for Pagination

The OFFSET clause is most often used for pagination in SQL queries. Pagination refers to displaying a portion of the results (e.g., the first 10 rows, the second 10 rows, etc.), especially in applications where results need to be presented on multiple pages.

In pagination scenarios, you adjust the OFFSET value based on the page number. Here's how you can paginate results, assuming you want to show 10 rows per page:

Example: Paginated Query (10 Rows per Page)

For Page 1 (rows 1–10):

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

For Page 2 (rows 11–20):

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

For Page 3 (rows 21–30):

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

And so on.

To calculate the correct OFFSET for any given page, use the formula:

OFFSET (page_number - 1) * page_size ROWS

Where:

·        page_number is the current page number (1, 2, 3, etc.).

·        page_size is the number of rows per page (in the example, 10).

3. Important Points About the OFFSET Clause

·        ORDER BY Clause is Required: The OFFSET clause must be used with an ORDER BY clause. Without an ORDER BY, the result set may not be ordered consistently, making the concept of "skipping" or "paging" meaningless.

Example:

SELECT * FROM employees

OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;  -- This will not work without ORDER BY

·        OFFSET can be Used without FETCH: While the FETCH clause is usually paired with OFFSET to limit the number of rows returned, OFFSET can be used by itself to skip a specific number of rows, although this isn't common.

Example (with no FETCH):

SELECT * FROM employees

ORDER BY hire_date

OFFSET 10 ROWS;  -- Skips the first 10 rows and returns all subsequent rows

·        Negative Offset: The OFFSET value must be zero or a positive integer. Using a negative value will result in an error.

Example (invalid query):

SELECT * FROM employees

ORDER BY hire_date

OFFSET -10 ROWS;  -- Invalid, OFFSET cannot be negative

·        Performance Considerations: When working with large datasets, using OFFSET can sometimes cause performance issues because the query must retrieve the full dataset before applying the skip and fetch logic. To optimize, ensure the query is filtered early (with a WHERE clause) and that it has proper indexes, especially on the columns used in ORDER BY.

·        Default Values for OFFSET: If you do not specify the OFFSET clause, the query will return all rows without skipping any. By default, this is equivalent to OFFSET 0 ROWS.

4. Common Patterns and Examples

4.1. Fetching the First n Rows (Without OFFSET)

If you only want to retrieve the first n rows from a result set, you can use FETCH FIRST without OFFSET.

Example:

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

FETCH FIRST 5 ROWS ONLY;  -- Retrieves the first 5 rows

4.2. Fetching Rows after Skipping a Certain Number (with OFFSET)

To skip a certain number of rows and fetch the next few, you use both OFFSET and FETCH.

Example:

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- Skips 20 rows, retrieves the next 10

4.3. Combining OFFSET with FETCH ALL

If you want to skip rows and then retrieve all remaining rows, you can use FETCH ALL ROWS ONLY instead of specifying a number.

Example:

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 50 ROWS FETCH ALL ROWS ONLY;  -- Skips the first 50 rows, retrieves all remaining rows

5. Limitations and Considerations

·        No "Backward" Pagination: The OFFSET clause doesn’t support going backwards in the result set (i.e., there is no direct way to "go back" a few rows). For that, you would need to adjust your OFFSET dynamically as the user navigates through pages.

·        Not Ideal for Large Result Sets: For large datasets, OFFSET can become inefficient, especially when you need to skip many rows (e.g., OFFSET 1000000 ROWS). In such cases, it may be better to use techniques like keyset pagination (also known as "seek method") where you keep track of the last seen record's key.

6. Real-World Example: Implementing Pagination in a Web Application

Let’s say you want to implement a paginated table of employees in a web application, where each page displays 10 employees, ordered by hire_date. Here’s how you would query the database for each page:

Page 1 (Display rows 1 to 10):

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Page 2 (Display rows 11 to 20):

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Page 3 (Display rows 21 to 30):

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY hire_date

OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Conclusion

The OFFSET clause in Oracle is a powerful and flexible tool for implementing pagination and controlling the number of rows returned by a query. It is most commonly used with the FETCH clause, making it easy to skip rows and return a specific subset of data. However, for large datasets or complex queries, consider performance implications and possibly explore alternatives like keyset pagination.