How to Efficiently Limit and Offset Query Results in Oracle?

Mastering Row Limiting and Offsetting in Oracle Queries

Oracle's ROWNUM pseudocolumn, frequently used for result set limitation, presents a unique challenge when combined with ordering. Unlike MySQL's LIMIT clause, which applies after sorting, ROWNUM filters before the ORDER BY clause is processed. This means simply using ROWNUM won't guarantee a specific ordered subset of your results.

The Standard Approach (All Oracle Versions):

To achieve the equivalent of MySQL's LIMIT functionality, use a nested query:

<code class="language-sql">SELECT *
FROM (
    SELECT *
    FROM emp
    ORDER BY sal DESC
)
WHERE ROWNUM <= 5;</code>

This approach first orders the data and then applies the ROWNUM filter, ensuring the top 5 highest salaries are returned.

Offsetting and Limiting Results:

For more sophisticated control, specifying both an offset and limit requires a slightly more complex query:

<code class="language-sql">SELECT *
FROM (
    SELECT a.*, ROWNUM rnum
    FROM (
        SELECT *  -- Your main query with ORDER BY clause
        FROM some_table
        ORDER BY some_column
    ) a
    WHERE ROWNUM <= :MAX_ROW_TO_FETCH
)
WHERE rnum >= :MIN_ROW_TO_FETCH;</code>

Replace :MAX_ROW_TO_FETCH and :MIN_ROW_TO_FETCH with your desired upper and lower bounds. This fetches rows within a specific range after ordering.

Oracle 12c and Beyond: The FETCH Clause:

Oracle 12c (and later versions) offer a more elegant solution using the FETCH clause:

<code class="language-sql">-- Retrieve the first 10 rows
SELECT *
FROM sometable
ORDER BY name
FETCH FIRST 10 ROWS ONLY;

-- Retrieve rows 20-30
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;</code>

The FETCH clause provides a cleaner and more readable way to limit and offset results, making it the preferred method for Oracle 12c and later. For older versions, the nested query approach remains necessary.

The above is the detailed content of How to Efficiently Limit and Offset Query Results in Oracle?. For more information, please follow other related articles on the PHP Chinese website!