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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments