Quantcast
OracleBrains.Com header image 2

How ROWNUM Pseudocolumn works?

April 23rd, 2012 by Rajender Singh · No Comments

I keep coming across guys confused about “How ROWNUM Pseudocolumn works?”

Even though ORACLE document clearly mentions “the ROWNUM pseudocolumn returns a number indicating the order in which ORACLE selects the row from a table or set of joined rows”, confusion still exists….may be we tend to google rather than going though ORACLE documents these days :)

As per above statement it is clear that ORACLE assigns ROWNUM after WHERE clause but before ORDER BY clause.

For example, if we run following query:

SELECT rownum, ename FROM emp WHERE ROWNUM < 10 ORDER BY ename;

If no index exist, ROWNUM be allocated by the ORACLE when it process WHERE condition i.e. “SELECT rownum, ename FROM emp WHERE ROWNUM < 10"

After it only ORACLE will process ORDER BY clause i.e. "ORDER BY ename",

so end result, ROWNUM may not follow the ORDER BY clause

Example as follows:

SQL> SELECT rownum, ename FROM emp WHERE ROWNUM < 10 ORDER BY ename;

ROWNUM ENAME
---------- ----------
2 ALLEN
6 BLAKE
7 CLARK
4 JONES
9 KING
5 MARTIN
8 SCOTT
1 SMITH
3 WARD

9 rows selected.

If we embed the ORDER BY clause in a subquery and place the ROWNUM in the main query, then we can force the ROWNUM to be assigned after ORDER BY.

Example as follows:

SQL> SELECT rownum, ename FROM ( SELECT ename FROM emp WHERE ROWNUM < 10 ORDER BY ename)

ROWNUM ENAME
---------- ----------
1 ALLEN
2 BLAKE
3 CLARK
4 JONES
5 KING
6 MARTIN
7 SCOTT
8 SMITH
9 WARD

9 rows selected.

Now in case the column which we are doing has a Index, then ORACLE will select the row using the INDEX, as a result, ROWNUM will follow the ORDER BY clause.

Example as follows:

SQL> SELECT rownum, empno FROM emp WHERE ROWNUM < 10 ORDER BY empno;

ROWNUM EMPNO
———- ———-
1 7369
2 7499
3 7521
4 7566
5 7654
6 7698
7 7782
8 7788
9 7839

9 rows selected.

I really don’t know how important you feel to understand this concept, but I did well by learning this in my early days itself :)

Tags: SQL and PL/SQL

0 responses so far ↓

  • There are no comments yet...Kick things off by filling out the form below.

Leave a Comment