Pagination in Oracle using ROWNUM and Limiting Result Set

pagination

ROWNUM is a magic column in Oracle Database that gets many people into trouble. When you learn what it is and how it works, however, it can be very useful. I use it for two main things:

  • To perform top- N processing. This is similar to using the LIMIT clause, available in some other databases.
  • To paginate through a query, typically in a stateless environment such as the Web. We can use this.

How ROWNUM Works?

ROWNUM 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 (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing. Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

SELECT * FROM t WHERE ROWNUM > 1;
Code language: SQL (Structured Query Language) (sql)

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure:

SELECT ..., ROWNUM FROM T WHERE <WHERE CLAUSE> GROUP BY <COLUMNS> HAVING <HAVING CLAUSE> ORDER BY <COLUMNS>;
Code language: SQL (Structured Query Language) (sql)

Think of it as being processed in this order:

  1. The FROM/WHERE clause goes first. 
  2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause. 
  3. SELECT is applied. 
  4. GROUP BY is applied. 
  5. HAVING is applied. 
  6. ORDER BY is applied.

That is why a query in the following form is almost certainly an error:

SELECT * FROM emp WHERE ROWNUM <= 5 ORDER BY sal DESC;
Code language: SQL (Structured Query Language) (sql)

The intention was most likely to get the five highest-paid people—a top- N query. What the query will return is five random records (the first five the query happens to hit), sorted by salary. The procedural pseudocode for this query is as follows:

ROWNUM = 1 FOR x in (SELECT * FROM emp) LOOP exit when NOT(ROWNUM <= 5) OUTPUT record to temp ROWNUM = ROWNUM+1 end loop SORT TEMP
Code language: SQL (Structured Query Language) (sql)

It gets the first five records and then sorts them. A query with WHERE ROWNUM = 5 or WHERE ROWNUM > 5 doesn’t make sense. This is because a ROWNUM value is assigned to a row during the predicate evaluation and gets incremented only after a row passes the WHERE clause. Here is the correct version of this query:

SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE ROWNUM <= 5;
Code language: SQL (Structured Query Language) (sql)

This version will sort EMP by salary descending and then return the first five records it encounters (the top-five records). As you’ll see in the top- N discussion coming up shortly, Oracle Database doesn’t really sort the entire result set—it is smarter than that—but conceptually that is what takes place. For pagination, if you want the 5 -10 records of the employee order by hiredate asc then go for this.

SELECT outer.* FROM (SELECT ROWNUM rn, inner.* FROM ( SELECT e.* FROM employee e ORDER BY hiredate) inner) outer WHERE outer.rn >= 5 AND outer.rn <= 10
Code language: SQL (Structured Query Language) (sql)

References

Oracle ROWNUM Documentation

Get our Articles via Email. Enter your email address.

You may also like...

4 Comments

  1. Apollo says:

    Very good article on the rownum. Seriously, it gives a good insight of the cocept of the rownum.
    And the last program is a good creation of the program and the use of the aliases. Hats off ..!!

  2. Arahant says:

    Thanks a lot! The last solution works beautifully.
    Thank you once again

  3. Milos says:

    Great article, thanks a lot!

  4. Raj says:

    Thanks a lot, the article was very helpful for fixing an issue with my query. It helped me a lot at a critical juncture.

Leave a Reply

Your email address will not be published. Required fields are marked *