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.

This table show an example, with Employee ID, First Name and Revenue

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.