RE: ROWNUM strangeness?--SOLVED

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: naveen_nahata_at_mindtree.com

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: ListGuru_at_fatcity.com (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: Dan.Fink_at_mdx.com

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: ListGuru_at_fatcity.com (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).