Explain the basic principles of oracle paging query, and explain the oracle paging Query

Explain the basic principles of oracle paging query, and explain the oracle paging Query

This article analyzes in detail the basic knowledge of oracle paging Query Based on the Data Query principle and paging implementation method. The following is the content of this article:

Cause 1

By default, oracle generates rowmun and rowid fields for each table. These fields are called pseudo columns.

1. Create a test table

CREATE TABLE TEST(ID NUMBER,NAME VARCHAR2(20))

2. Insert Test Data

Insert into test values (1, 'zhang san'); insert into test values (2, 'Li si'); insert into test values (3, 'wang wu '); insert into test values (4, 'zhao 6'); insert into test values (5, 'zheng 7'); insert into test values (6, 'hu 8 '); insert into test values (7, 'Liu jiu ');

3. view the table fields and check the included fields.

select rowid,rownum,id,name from TEST; 

4 rowid is generally not used. Oracle is used internally to store the physical location of rows. rownum, or row number, is related to the page.

II

1. query the rows smaller than 5 and query four results.

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

2. query rows larger than 2 and smaller than 5

select rownum,id,name from test where rownum>2 and rownum <5;

I did not find anything. Why ?,

Rownum has the following features:

1 ROWNUM is only applicable to values smaller than or less than or equal to 1. If the value is equal to or equal to 1, the value can only be equal to 1;

2 ROWNUM is the number of rows sequentially allocated by the oracle system. The first row returned is 1, and the second row is 2;

3 ROWNUM always starts from 1

4 if the row number of the first data is 1 and does not meet the condition of> 2, the first row is removed and the second row is changed to the new first row, the conditions cannot be met, so no data can be found.

3. Correct syntax: Because> it cannot be used, the row number is queried in the inner layer as a result set and compared with the inner result set in the outer layer.

select rownum,id,name from( select rownum rn, u.* from test u where rownum<5) unwhere un.rn>2

4 if the page is paginated, for example, if you want to query the second page for three rows on each page, it is equivalent to querying 4, 5, 6 rows, starting line 4 = (page number-1) * length of each page + 1, end Line 6 = page number * length of each page

select rownum,id,name from (  select rownum rn , t.* from test t where rownum <=6) nwhere n.rn>=4

5. Similarly, you can change the query in 4 to the most common layer-3 structure.

Select rownum, id, name from (select rownum rn, n. * from (select * from test -- how to write the innermost loop) n where rownum <= 6 -- less than the limit written on the second layer) where rn> = 4