Limiting and Offsetting Rows Returned
This is a short description.
You can use theFETCH
and OFFSET
clauses to constrain the
number of rows returned by the SELECT
statement and
to skip a specified number of rows from the beginning of the result
set. Both clauses are optional and can be used together, or independently.
The fetch and offset clauses are part of the SELECT
statement and are placed at the end.
These clauses are useful for situations where you have a large result set (such as with a large dimension), and you want to present, for example, the first 100 rows to the user. The Oracle BI Server stops processing when the limit is reached, improving overall performance and conserving resources. In addition, the limit is pushed to the back-end database in many cases so that the database can optimize the query.
Technically,
both clauses can be used without an ORDER BY
clause,
but the results would be non-deterministic. Because of this, both
clauses should always be used with ORDER BY
.
If OFFSET
is not specified, the default value
is 0, which means that results are returned starting from the first
row. If FETCH
is not specified, it means that there
is no limitation on the number rows returned.
Both clauses
are evaluated after the WHERE
clause, aggregation, HAVING
clause, window analytic function, and ORDER
BY
clause.
Syntax for OFFSET Clause
OFFSET n ROW[S]
n
is the number of rows you want to skip from
the beginning of the result set. Note that n
must
be greater than zero.
Syntax for FETCH Clause
FETCH FIRST | NEXT n ROW[S] ONLY
n
is the number of rows you want to skip from
the beginning of the result set.. Note that n
must
be greater than zero.
Typically, FIRST
is
used when the limit clause is used independently of the offset clause,
while NEXT
is used when the limit clause is used
in conjunction with the offset clause.
SELECT employeeid, firstname, revenue
FROM sales.employee
ORDER BY revenue desc
OFFSET 2 ROWS
FETCH NEXT 4 ROWS ONLY0
The following table lists the
entire result set without the OFFSET
and FETCH
clauses. When the OFFSET
and FETCH
clauses are included, only the rows shown in bold are returned.
Employeeid | FirstName | Revenue |
---|---|---|
4 | Margaret | 250187 |
3 | Janet | 213051 |
1 | Nancy | 202143 |
2 | Andrew | 202143 |
7 | Robert | 177749 |
8 | Laura | 141295 |
9 | Annie | 133301 |
6 | Michael | 82964 |
5 | Steven | 78198 |
Limitations of the FETCH and OFFSET Clauses
Because ORDER BY
clauses are ignored in UNION ALL
set-operator blocks, using these clauses in such
queries would be non-deterministic. Do not use FETCH
and OFFSET
with these queries.