In an Oracle database, what is the difference between ROWNUM and ROW_NUMBER?

What is ROWNUM and ROW_NUMBER ?

From stackoverflow Bob Dizzle

• ROWNUM is a "pseudocolumn" that assigns a number to each row returned by a query:

  SQL> select rownum, ename, deptno
    2  from emp;
  
      ROWNUM ENAME          DEPTNO
  ---------- ---------- ----------
           1 SMITH              99
           2 ALLEN              30
           3 WARD               30
           4 JONES              20
           5 MARTIN             30
           6 BLAKE              30
           7 CLARK              10
           8 SCOTT              20
           9 KING               10
          10 TURNER             30
          11 FORD               20
          12 MILLER             10
  

  ROW_NUMBER is an analytic function that assigns a number to each row according to its ordering within a group of rows:

  SQL> select ename, deptno, row_number() over (partition by deptno order by ename) rn
    2  from emp;
  
  ENAME          DEPTNO         RN
  ---------- ---------- ----------
  CLARK              10          1
  KING               10          2
  MILLER             10          3
  FORD               20          1
  JONES              20          2
  SCOTT              20          3
  ALLEN              30          1
  BLAKE              30          2
  MARTIN             30          3
  TURNER             30          4
  WARD               30          5
  SMITH              99          1
  

  From Tony Andrews

• From a little reading, ROWNUM is a value automatically assigned by Oracle to a rowset (prior to ORDER BY being evaluated, so don't ever ORDER BY ROWNUM or use a WHERE ROWNUM < 10 with an ORDER BY).

  ROW_NUMBER() appears to be a function for assigning row numbers to a result set returned by a subquery or partition.

  Ron : order by is ok e.g., select * from (select a,b,c from foo order by d) where rownum < 10

  R. Bemrose : @Ron: Try it. You'll find out the hard way that it takes the first 10 results from the entire result set, then orders those.
  From R. Bemrose

• rownum is a pseudocolumn which can be added to any select query, to number the rows returned (starting with 1). They are ordered according to when they were identified as being part of the final result set. (#ref)

  row_number is an analytic's function, which can be used to number the rows returned by the query in an order mandated by the row_number() function.

  David Aldridge : -1 for "They are numbered in the order that the rows are returned in". Not true -- they are ordered according to when they were identified as being part of the final result set, so that is prior to an ORDER BY clause
  From Michael OShea