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

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          1KING               10          2MILLER             10          3FORD               20          1JONES              20          2SCOTT              20          3ALLEN              30          1BLAKE              30          2MARTIN             30          3TURNER             30          4WARD               30          5SMITH              99          1

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.

Apart from the other differences mentioned in answers, you should also consider performance. There is a non-authoritative but very interesting report here, comparing various means of pagination, among which the use of ROWNUM compared to ROW_NUMBER() OVER():

http://www.inf.unideb.hu/~gabora/pagination/results.html