Prior to Oracle12c, you had to use special techniques to
display the first "n" number of rows within a query.
Getting sub-sets of data in sorted order from the
database has always been problematic in SQL, and many people
use the "where rownum , n" clause, randomly
displaying rows as they are found in the data blocks.
Oracle has many ways to display the top n
rows from a table, but none off them has straightforward
syntax until Oracle introduced the "row limit" functionality
with the "fetch:" and "offset" syntax.
.Here is a review of the fetch top-n SQL methods
in Oracle:
-
Row Limit plan: This
Oracle 12c new feature
offset
x fetch
first y rows only makes it easy to display the first n
rows from a table. That is the method that we
discuss below.
Prior to
Oracle 12c, we were constrained by these methods:
- Top-n SQL using
subselect
with ROWNUM. You can use an inline view with
ROWNUM to get the top-10 rows for any SQL query, and the
rownum can be used to materialize an in-line view.
Beware, this is not always a good thing, as adding the
where rownum WITH clause.
- Top-n SQL with dense_rank and
SQL ranking functions. Oracle SQL includes
ranking functions that provide support for common OLAP
rankings, such as the top 10, bottom 10, top 10 percent,
and bottom 10 percent.
- Top-n SQL using the
row_number
function: You can query the top 100 rows using
the Oracle row_number() and "over" syntax.
Let's consider a way to display the top n rows from a
table:
select
*
from
(select empno, sal
row_number()
over
(order by
sal desc)
rnk from emp)
where rnk <= 10;
This works to display the first 10 rows from the table,
but the syntax is cryptic and in Oracle 12c we get a SQL
extension that makes it easy and straightforward when
display the first n rows from a table.
-
select . . . . order by x fetch first 10 rows
only: This will display the first rows of the
table, in the order that was specified in the order
by clause.
-
select . . . order by x offset 20 fetch first
10 rows only: This will offset into the table
(in sorted order) and then fetch the next 10 rows of the
table.
Consider these examples of :
--
************************
-- fetch first 5 rows,
--
ordered by ename
-- ************************
select
ename
from
emp
order by ename
fetch first 5 rows
only;
ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
-- ************************
-- fetch next 5 rows,
--
start a 5th sorted row
-- ordered by ename
-- ************************
select
ename
from
emp
order by ename
offset 5 rows
fetch next
5 rows only;
ENAME
----------
JAMES
JONES
KING
MARTIN
MILLER
Here we see that Oracle 12c has again extended their
dialect of SQL to make it easier to paginate a SQL result
set and reduce the amount of cryptic ANSI 99 syntax that was
previously required to display the "next n" and "first n"
rows from a pre-sorted result set.
Performance of offset and fetch
statements
In PL/SQL, a programmer could declare a cursor and fetch
a page of data using the "fetch" syntax, and this SQL
"fetch" appears to have similar functionality. While
Oracle does not publish the internals of these offset and
fetch operands, that manifest themselves as a "row limit" in
execution plans.
This suggests that there will be some performance gain to
using this syntax, especially with the "result set" syntax,
that will prevent the need for the SQL to fetch the data
multiple times.
