nOTE : [ reference :http://blog.lishman.com/2008/03/rownum.html]
ROWNUMis an Oracle pseudo column which numbers the rows in a result set.
Here is a summary of howSELECT rownum, table_name FROM user_tables;ROWNUM TABLE_NAME ------------- ----------------- 1 EMP 2 DEPT 3 BONUS 4 SALGRADE 5 DUMMY5 rows selected
ROWNUMcan be used.
ROWNUMcan be used to limit the number of rows returned by a query in a similar way to
LIMITin Postgres and MySql,
TOPin SQL Server and
FETCH FIRSTin DB2.
SELECT rownum, table_name FROM user_tables WHERE rownum <=3;ROWNUM TABLE_NAME ------------- ----------------- 1 EMP 2 DEPT 3 BONUS3 rows selected
ROWNUM with DMLThe use of
ROWNUMis not restricted to select statements. It can be used with DML statements that update the database too.
CREATE TABLE o AS SELECT * FROM all_objects WHERE rownum <= 1000;Table createdUPDATE o SET object_id = rownum, created = created + INTERVAL '1' MINUTE * rownum WHERE rownum <= 100;100 rows updatedDELETE FROM o WHERE OWNER = 'SYS' AND rownum = 1;1 rows deleted
ROWNUMis particularly useful, when used in conjunction with the
CONNECT BY LEVELclause, for creating arbitrary rows in the database. See the article on generating rows in Oracle for more details.
Offsetting RowsRows can also be skipped at the beginning of a result set using
You will notice that an inline view has been introduced to transform theSELECT rnum, table_name FROM (SELECT rownum rnum, table_name FROM user_tables) WHERE rnum > 2;RNUM TABLE_NAME -------- ---------------- 3 SALGRADE 4 DUMMY 5 DEPT3 rows selected
ROWNUMpseudo column into a 'real' column before we do the comparison.
It is tempting to write the above SQL as follows.
However, this query will always return zero rows, regardless of the number of rows in the table.SELECT table_name FROM user_tables WHERE rownum > 2;TABLE_NAME ------------------------------0 rows selected
To explain this behaviour, we need to understand how Oracle processes
ROWNUM. When assigning
ROWNUMto a row, Oracle starts at 1 and only only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that
ROWNUMis greater than 2, no rows are selected and
ROWNUMis never incremented beyond 1.
The bottom line is that conditions such as the following will work as expected.
.. WHERE rownum = 1;
.. WHERE rownum <= 10;
While queries with these conditions will always return zero rows.
.. WHERE rownum = 2;
.. WHERE rownum > 10;
Top-n QueryTypically, a top-n query sorts data into the required sequence and then limits the output to a subset of rows.
For example, suppose we wish to retrieve the top three earners from our employee table.
The inline view (the inner select) sorts the rows and passes the result up to the outer select. The outer select then limits the output to three rows.SELECT ename, sal FROM ( SELECT ename, sal FROM emp ORDER BY sal DESC) WHERE rownum <=3;ENAME SAL ---------- --------- KING 5000 SCOTT 3000 FORD 30003 rows selected
It may seem more natural to use the following SQL.
However, this does not give us the result we want because Oracle assigns theSELECT ename, sal FROM emp WHERE rownum <=3 ORDER BY sal DESC;ENAME SAL ---------- ---------------------- ALLEN 1600 WARD 1250 SMITH 8003 rows selected
ROWNUMvalues to the rows before it does the sort.
In this example, Oracle will retrieve three rows from the table, any three rows, and sort only these three rows. We really need Oracle to sort all the rows and then return the first three. The inline view will ensure that this will happen.
Sort PerformanceLimiting rows on a sorted result set using
ROWNUMcan also provide an added performance benefit. Rather than physically sorting all the rows to retrieve just the top few, Oracle maintains an array which contains just the highest or the lowest values (depending on whether we specified ASC or DESC in the ORDER BY clause). The size of the array will be the number of rows we wish to return. As rows are processed, only the highest (or lowest) values are retained in the array. All other rows are discarded.
PaginationNext, we will see how
ROWNUMis used to select a range of rows from within a result set. This is useful if we are to provide pagination on a web screen, for example.
Suppose we are paging through the employee table in name order and we wish to display rows six to ten inclusive.
We use nested inline views to retrieve and sort the data and then apply the range check usingSELECT rnum, ename, job FROM (SELECT /*+ FIRST_ROWS(10) */ rownum rnum, ename, job FROM (SELECT ename, job FROM emp ORDER BY ename) WHERE rownum <= 10 ) WHERE rnum > 5;RNUM ENAME JOB -------- ---------- --------- 6 JAMES CLERK 7 JONES MANAGER 8 KING PRESIDENT 9 MARTIN SALESMAN 10 MILLER CLERK5 rows selected
ROWNUM. We have split the upper and lower bound check, which allows Oracle to use
COUNT(STOPKEY)in the execution plan when checking for
ROWNUM <= 10. This is a performance optimization which, along with the sorting optimization described earlier, will ensure that our query runs efficiently as the table grows.
FIRST_ROWS(n)hint also tells Oracle to optimize the query so that the first n rows are returned as quickly as possible.
ROWNUMprovides a mechanism for returning a subset or range of rows from a query. It can be misleading at first if not properly understood but, once mastered, is invaluable for limiting result set output for pagination and top-n style queries.