Oracle RowNum Ultimate Explanation __oracle

Oracle rownum pseudo fields are used more frequently, especially in pagination, but because of its particularity many people may not understand its actual behavior, the network has many articles to introduce this rownum usage, but many are speaking very vague, or is not too comprehensive, Everyone looks very painful, here I will give you a comprehensive explanation, I hope to help you:

First of all, we have to recognize the particularity of several rownum:

First, the rownum is in the record output time only step by step generation.
Second, if the record output is not satisfied before the condition, then the rownum is always starting from 1.

Remember the above two points, basically you are very familiar with the use of this pseudo field, below I will specifically analyze these characteristics. We examine the following SQL statements:

SELECT * from Test where rownum >=1;    



 SELECT * from Test where rownum >=2;     



 SELECT * from Test where rownum <= 10;   

Suppose the test table has 15 records, here the first SQL Output 15 records, 2nd SQL output 0, 3rd SQL output 10 records.

Why, then? As I said above, because of its particularity, RowNum is produced when recording output, so we follow the following steps to

Processing:

1 Any rownum that appears on the same SQL can be ignored, and then the three SQL statements are only

SELECT * FROM Test    

2 We output the record at this time, when the output of the first record, Oracle will arrange his rownum=1, said earlier

(If the record output is not met before the condition, then the rownum will always start at 1)

3 This time with rownum where conditions to filter this record is satisfied, we found that only the first SQL and 3rd SQL meet Rownum>=1 and rownum<=10, and 2nd SQL rownum>= 2, it is obvious that the record does not meet the condition because the first record of the output is rownum=1.

4 output 2nd record, at this time 1th SQL and 3rd SQL because there is a record to meet the conditions in the front, at this time rownum will be arranged into 2,

Clearly the 2nd record also meets the requirements. Let's look at the 2nd SQL statement and we'll output the 2nd record at this point, but because one of the previous outputs

Record was rejected by it, that is not satisfied with the conditions, then the output of the 2nd record rownum still equal to 1, attention oh, many people do not understand why, its

It is very simple, if the previous record output is not satisfied, then the rownum will always start from 1, and only after the previous output of a record to meet the criteria, the subsequent record rownum increase. --Simple, so here for the 2nd SQL, his record rownum is still equal to 1, and does not meet the conditions.

5 record the processing of other records.

Follow the above method and you can process any SQL about rownum, such as:

SELECT * from Test where rownum >=1  

In the above method, first remove rownum and ordered by then leave the select * from Test where id>5; We're done here.

Output results, and then loop the processing of each record, there are rownum to meet the conditions increase, the record does not meet the conditions will not increase. The results are then sorted. Just so

Simple. Why do I have to get rid of the order by? Because the sort is the result that comes out to sort, and when the result comes out, the rownum is already allocated. So, if the above statement prints the last rownum is a sequence.

It is also important to note that this rownum process is for the same level of SQL, where select and RowNum are at the same level

can do that. If it is nested SQL, special case analysis.

For example:

SELECT * FROM (SELECT * to Test where id>5 and rownum<=6) t where rownum >=1 ORDER by t.id ASC

The color of the above is at the same level.