To be exact...
Rownum is sequentially assigned for each row that meets the predicate. If
the row does not match the predicate condition, it is discarded and not
assigned a rownum. Below is an example using our friendly EMP table.
As for sorting, rownum is assigned before any sorting. However, if the data
is read via an index, the rownum appears to be assigned after the sort.
SQL> l
1 select rownum, empno, ename, deptno
2* from emp
SQL> /
ROWNUM EMPNO ENAME DEPTNO
---------- ---------- ---------- ----------
1 7369 SMITH 20
2 7499 ALLEN 30
3 7521 WARD 30
4 7566 JONES 20
5 7654 MARTIN 30
6 7698 BLAKE 30
7 7782 CLARK 10
8 7788 SCOTT 20
9 7839 KING 10
10 7844 TURNER 30
11 7876 ADAMS 20
12 7900 JAMES 30
13 7902 FORD 20
14 7934 MILLER 10
15 9999 null1
16 9998 null2
16 rows selected.
SQL> 2
2* from emp
SQL> i
3 where rownum < 5
4
SQL> l
1 select rownum, empno, ename, deptno
2 from emp
3* where rownum < 5
SQL> /
ROWNUM EMPNO ENAME DEPTNO
---------- ---------- ---------- ----------
1 7369 SMITH 20
2 7499 ALLEN 30
3 7521 WARD 30
4 7566 JONES 20
1 select rownum, empno, ename, deptno
2 from emp
3 where rownum < 5
4* and deptno = 30
SQL> /
ROWNUM EMPNO ENAME DEPTNO
---------- ---------- ---------- ----------
1 7499 ALLEN 30
2 7521 WARD 30
3 7654 MARTIN 30
4 7698 BLAKE 30
1 select rownum, empno, ename, deptno
2 from emp
3 where rownum < 5
4 and deptno = 30
5* order by ename
SQL> /
ROWNUM EMPNO ENAME DEPTNO
---------- ---------- ---------- ----------
1 7499 ALLEN 30
4 7698 BLAKE 30
3 7654 MARTIN 30
2 7521 WARD 30
-----Original Message-----
Sent: Thursday, August 29, 2002 11:44 AM
To: Multiple recipients of list ORACLE-L
>As we all know, ROWNUM is *****usually***** assigned when rows are
>read into the cache, before sorting;
Always
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
