How to do Pagination in Oracle Database - SQL Query With Example

Many times we need an SQL query that returns data page by page i.e. 30 or 40 records at a time, which can be specified as the page size. In fact, Database pagination is a common requirement of Java web developers, especially dealing with the largest data sets.  In this article, we will see how to query Oracle 10g database for pagination or how to retrieve data using paging from Oracle. Many Java programmer also uses display tag for paging in JSP which supports both internal and external paging. In the case of internal paging, all data is loaded into memory in one shot and the display tag handles pagination based upon page size but it is only suitable for small data where you can afford those many objects in memory.

If you have hundreds of rows to display then it's best to use external pagination by asking the database to do pagination. In pagination, ordering is another important aspect that can not be missed.

It’s virtually impossible to sort the large collections in Java using Comparator or Comparable because of limited memory available to the Java program, sorting data in a database using the ORDER BY clause itself is a good solution while doing paging in a web application.

But, if you are new to the SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

Pagination SQL query in Oracle 10g database with Example 

In database paging, we only query data which we required to show or may be up to 3 page just to prefetch some data in advance for performance reason.

Thankfully Oracle database provides a convenient method row_number() which can be used to provide a unique row number to each row in result set. See Oracle PL/SQL Fundamentals - Part 1 to learn more about row_number and other pagination methods in Oracle database.

By including row_number() in the query you can produce a result set which is numbered and then its just a job to retrieve data from specified indexes or pages. Here is an example of pagination query in Oracle 12c database:

SELECT * FROM (
    SELECT
      ord.*,
      row_number() over (ORDER BY ord.order_id ASC) line_number
    FROM Orders ord
 
  ) WHERE line_number BETWEEN 0 AND 5  ORDER BY line_number;

This will print the result of a query including an additional column called line_number which will automatically be populated by Oracle because of row_number() function.

You can also see Oracle Database 12c Fundamentals learn more about the row_number function of Oracle 12c database. A free online course from Pluralsight to learn Oracle database in detail. There are two parts of this tutorial, both are freely available once you signup for 10-day free trial on Pluralsight website.

By using this line_number column now we can get results page by page based on the size of the page or more especially from one row to another like from 1 to 30 or 5th to 30 whatever since you can pass starting row and end row from Java program to Oracle database.

Though I know MySQL database has inbuilt paging support using the LIMIT keyword, this row_number() function of Oracle is equally useful for paging in the Oracle database.

I have also found that SQL Server also supports the row_number() function, which can be used to get data page by page in SQL Server. Btw, if you are preparing for Oracle Certification e.g. 1Z0-066 to become a certified Oracle Database administrator then you can check out  David Mayer's free 1Z0-066 dumps for reference. 

Further Learning

How to find second highest salary in SQL – Interview question

How to find duplicate records in a table in the database

Write SQL query to join three tables in MySQL

Difference between correlated and noncorrelated subquery in Java

Thanks for reading this article, if you like this article then please share it with your friends and colleagues. If you have any questions or feedback then please drop a comment.