Help using ROWNUM in queries

I ran the following within Oracle SqlPlus prompt (SQL*Plus: Release 8.1.7.0.0 on SunOS 5.6 Generic_105181-34 sun4u sparc SUNW,Ultra-4): SQL> select count(*) from a_table;

 COUNT(*)
 ----------
     20921

SQL> delete a_table where rownum > 400;

0 rows deleted.

SQL> delete a_table where rownum < 400;

399 rows deleted.

Why is that?

The ROWNUM field is called a "psuedo-column". It really doesn't exist. When you use ROWNUM in your queries, keep in mind that the row numbers are not assigned until the very end of your query. For instance, I can issue the following query:

SELECT ROWNUM, empno FROM emp WHERE empno < 1000;

This may give me the following output:

ROWNUM  EMPNO
------  -----
     1    998
     2    997
     3    901

Now if I order this data, I can get the same employee numbers with different row numbers:

SELECT ROWNUM, empno FROM emp WHERE empno < 1000 ORDER BY empno;

ROWNUM  EMPNO
------  -----
     1    901
     2    997
     3    998

See how the row numbers differ for the same data? And notice what happens when I issue the following query:

SELECT ROWNUM, empno FROM emp WHERE empno > 1000;

ROWNUM  EMPNO
------  -----
     1   1001
     2   1010
     3   1007

The point of this simple exercise is that the row numbers are not the physical ordering of the rows in the table. They are the ordering of the "result set" of your query. So this can make us arrive at the "misleading" results that you've experienced.