Example of Oracle three-layer nested paging query and rownum principle, oraclerownum

Example of Oracle three-layer nested paging query and rownum principle, oraclerownum

For example, the data in the COMPONENT table is as follows:

1. Execute select * from (select com. *, rownum r_num from (select * from COMPONENT) com where rownum <8) where r_num> 2

2. Execute select * from (select com. *, rownum r_num from (select * from COMPONENT) com where rownum = 1) where r_num <5

3. select * from (select com. *, rownum r_num from (select * from COMPONENT) com where rownum = 2) where r_num <5

 

Cause:

Rownum is the amount dynamically generated by oracle Based on the query. It does not look at the where Condition Clause before generating rownum. Therefore, when querying a result, rownum is initialized to 1 by oracle, and then the where clause is executed,

If it is true, it will continue. Otherwise, it will be discarded, which is the reason why rownum = 1 rownum <n is good.

Rownum = 2 is not good because:

1. Perform the query operation

2. Set row num of the first row to 1.

3. Compare the row num of the row with the condition. If the row num does not match, the row is discarded. If the row num matches, the row is returned.

4. oracle obtains the next row and adds rownum to 1.

5. Return to Step 1

Similarly, select rownum, id from COMPONENT where rownum> 2 does not return rows, because the select id from COMPONENT is queried first, and the rownum of row 1st is marked as 1, and then the where condition is viewed, if the value of 1 <2 is false, the row is discarded. If the value of 2nd rows is executed, or if the value of rownum is 1, the where condition or 1 <2 is false. Therefore, it is always false, and rownum does not change, all rows are discarded, so there is no result.