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 it
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, 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 sytax would be
| select * from ( select rownum rnum, a.* from (your_query) a where rownum <= M ) where rnum >= N; |
2) Using ROW_NUMBER() function: It behaves similarly to the ROWNUM pseudocolumn 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 MV
Hope you like the article on Top-N Queries and Pagination. Please do provide feedback