Analysis of Oracle Paging query principle

Reason one Oracle generates ROWMUN,ROWID fields for each table by default, and these fields we call pseudo-columns

1 Creating a Test table

CREATE TABLE TEST (ID number,name VARCHAR2 (20))

2 Inserting test data

INSERT into test values (1, ' Zhang San '); INSERT into test values (2, ' John Doe '); INSERT into test values (3, ' Harry '); INSERT into test values (4, ' Zhao Liu '); INSERT into test values (5, ' Zheng Qi '); INSERT into test values (6, ' Hu Eight '); INSERT into test values (7, ' Liu Jiu ');

3 viewing table fields, confirming the field that comes with it

Select Rowid,rownum,id,name from TEST;  

4 rowID is generally not used, Oracle internal to store the physical location of the row, and paging related to the rownum, that is, the line number

Two

1 query for rows less than 5, query for four results

Select  rowid,rownum,id,name from test where rownum <5;

2 query for rows greater than 2 less than 5

Select Rownum,id,name from Test where rownum>2 and RowNum <5;

Found nothing to find out, what is the reason?

RowNum has the following features:

1 rownum is only applicable to less than or less than equals, if it is equal to judgment, then can only be equal to 1;

2 RowNum is the number of lines assigned by the Oracle system sequentially, the first row returned is 1, the second row is 2, and so on;

3 rownum always starting from 1

4 The first Data line number is 1, does not conform to the condition of >2, then the first row is removed, the second line is changed to a new first row, so continue, until the last line, the condition is always not satisfied, so a data can not be found.

3 correct wording: because > can not use, the line number is queried as a result set by the inner layer, the outer layer is compared with the inner result set.

Select  rownum,id,name from  (select RowNum rn, u.* from Test u where rownum<5) Unwhere un.rn>2

4 if paging, such as three rows per page, to query the second page, then the equivalent of checking 4,5,6 bar, the starting Line 4 = (page 1) * page length +1, end line 6 = page number * per page length

Select Rownum,id,name from (       select RowNum rn, t.* from Test t where rownum <=6) Nwhere n.rn>=4

5 The same can change the 4 query to the most common three-tier structure

   Select Rownum,id,name from (       select RowNum rn, n.* from              (                 select * FROM Test  --How to write the inner loop              )  n where RowNum <=6  --less than the limit written on the second layer       )    where rn>=4   
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

Analysis of Oracle Paging query principle