Top-N Queries and Pagination in Oracle

Top-N Queries  and Pagination is common in Web-based applications. The user inputs a set of criteria, which runs a query and then lets the user click the Previous and Next buttons to page through the result set. To achieve this paging functionality, the application needs to be able to get a certain set of rows from the Database query.

Lets take a look at the different method in Oracle to achieve Top-N Queries in Oracle and Pagination in Oracle query

Pre 12c

(1) Using ROWNUM Clause

What is ROWNUM
It is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, … N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row .

Here is the way to obtain the top 5 values

SELECT *
FROM (SELECT *
FROM dept
ORDER BY sales DESC)
WHERE ROWNUM <= 5;

This version will sort Dept by sales descending and then return the first five records it encounters (the top-five records).
For pagination in oracle , if you want the 5 -10 records of the Dept order by sales desc then go for this.

SELECT a.*
FROM (SELECT ROWNUM rn, b.*
FROM ( SELECT *
FROM dept
ORDER BY sales dsc) b  where rn <=10) a
WHERE a.rn >= 5

The general syntax would be

select *
from
( select rownum rnum, a.*
from (your_query) a
where rownum <= M )
where rnum >= N;

(2) Using ROW_NUMBER() oracle analytic function: It behaves similarly to the ROWNUM pseudo column but is more flexible and has more capabilities

Here is the way to obtain the top 5 values

SELECT *
FROM (SELECT d.*,row_number() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 5;

Here is the query for Pagination

SELECT * FROM (
SELECT
d.*,
row_number() over (ORDER BY d.sales DSC) rn
FROM dept d) WHERE rn BETWEEN 0 AND 5 ORDER BY rn;

The above Top N queries will give return different records when two things tied for the spot when using the top n queries

(3) Using RANK() and DENSE_RANK():These are analytic function which can be used to remove the problem stated above
Here is the way to obtain the top 5 values using rank

SELECT *
FROM (SELECT d.*,rank() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 5;

Here is the way to obtain the top 5 values using dense_rank

SELECT *
FROM (SELECT d.*,dense_rank() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 5;

With 12c

Top-N feature:

Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses—together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set or, alternatively, the first N records after skipping over a set of records, so you can easily paginate through a result set

A Top-N query allows us to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set

Example:

SELECT value
FROM mytable
ORDER BY value DESC
FETCH FIRST 10 ROWS ONLY;  

select * from my_test order by name fetch first 3 rows only;

If you look at the optimizer plan for the above query, it is still using row_number() under the wrap for doing it

Pagination can also happen with this feature with the use offset syntax

– offset 10 rows fetch first 10 rows only

select * from my_test order by id
offset 10 rows fetch next 10 rows only;

– offset 10 rows fetch first 0.1 percent rows only

select * from my_test order by id offset 10 rows first 0.1 percent rows only;

– offset 10 rows fetch first 3 rows with ties. It means all the top rows with ties will also be included in the result

select * from my_test order by name fetch first 3 rows with ties;

If you check the optimizer plan of the above query, you will find optimizer is using rank() function as demonstrated above in Pre 12c case

Restriction
(1)If you have a SELECT statement with FOR UPDATE, you can’t use it.
(2)The SELECT statement can’t CURRVAL or NEXTVAL of sequences
(3) If the query of the Materialized Views has this clause, then you can’t do an incremental refresh of that Materialized View

Hope you like the article on Top-N Queries in oracle and Pagination in oracle query. Please do provide feedback

Also Reads
Lead Function in Oracle
RANK function in Oracle
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html