Rownum analysis, oraclerownum

Rownum analysis, oraclerownum

For the rownum problem in Oracle, many documents do not support>, >=, =, between... and. The preceding symbols can only be used (<, <= ,! =), Not to mention>,> =, =, .. and will prompt SQL syntax errors, but often cannot find a record, and it seems to be an inexplicable result. Next let's take a look at some meanings and common usage of rownum.

ROWNUM Concept

Assume that a table has 14 emp records.

select count(*) from emp;

If select rownum and empno from emp where rownum <10 are used, as long as the result is smaller than the number, it is easy to reach an agreement with the general understanding in terms of concept, and there should be no doubt.

If select rownum, empno from emp where rownum> 10 is used (if you write such a query statement, you should want to get the four records in the table in your mind ), you will find that the displayed results will disappoint you and no records are found.

Maybe you still suspect that no one has deleted some records and then checked the number of records, which is still 14? Where is the problem?

First understand ROWNUM, becauseROWNUM is a pseudo column added to the result set, that is, a column added after the result set is found.(Emphasis: you must first have a result set ).

select t.*, rownum from emp t;

In short, rownum is the serial number that matches the condition result.

It always starts from 1. Therefore, the result you select cannot be less than 1, and there are other values greater than 1. Therefore, you cannot expect the following result set:

11 7876
12 7900
13 7902
14 7934


Rownum> 10 no records,If the first clause does not meet the requirements, the ROWNUM of the second clause becomes 1, so there will never be a record that meets the conditions..

Or you can understand it as follows:

ROWNUM is a sequence in which oracle databases read data from data files or buffers. If it obtains the first record, the rownum value is 1, the second record is 2, and so on. If you use>,> =, =,... and, because the rownum of the first record obtained from the buffer or data file is 1, it is deleted and then removed. However, its rownum is still 1 and deleted, and so on.

ROWNUM symptom explanation

With the concept of rownum established from different aspects above, we can come to understand several images using rownum.

1. select rownum, empno from emp where rownum! = 10 why is the first nine data records returned?

It is the same as select rownum, c1 from tablename where rownum <10 returned result set?

Because after the result set is queried and 9th records are displayed, all subsequent records are displayed! = 10, or> = 10, so only the first nine records are displayed. It can also be understood that the value of rownum is 10 for the record after rownum is 9, because the condition is! = 10, so remove it. Then add the record, and the rownum is 10. If so, only the first nine records will be displayed.

2. Why can't we find a record when rownum> 1, while rownum> 0 or rownum> = 1 always displays the records
Because rownum is added after the queried result set, it always starts from 1.

3. Why between 1 and 10 or between 0 and 10 can be found, but between 2 and 10 cannot be obtained
The reason is the same as above, because rownum always starts from 1.

4. oracle Paging

It can be seen from the above that it is wrong to discard the rownum = 1 record at any time, and it is indispensable in the result set. If rownum = 1 is missing, it cannot exist like a castle in the air, therefore, your rownum condition must include 1.

However, if you want to use rownum> 10, you need to use nested statements to convert Mr. rownum into and then query it.
Select *
From (selet rownum as rn, t1. * from a where ...)
Where rn> 10



PS: rownum cannot be prefixed with any base table name.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.