Shulou(Shulou.com)06/01 Report--

Tables in Oracle, in addition to the fields we designed when we built the table, actually have two fields (only two are introduced here), ROWID (row identifier) and ROWNUM (row number). Even if we look at the table structure using the DESCRIBE command, we can't see the description of these two columns, because they are actually only used inside the database, so they are often called pseudo columns.

Create a table with only two fields (id, col). Using the describe command to look at the table structure, you can see that there are indeed only two fields from when the table was created. But when we can query, we can find the value of the pseudo-column.

select rowid,rownum,id,col from table;

This rowid we generally do not use, Oracle database internal use it to store the physical location of the row, is an 18-bit number, using base-64 encoding. And rownum, which we use for pagination queries, is the line number of the row represented.

For paging queries, we just need to find a way to query from a certain starting line to the end line, and the paging logic can be placed inside the program. So, of course, we think of the following statement to query the data on page 2 (2 data per page, the page number starts from 1, so the starting line number is (page number-1)* page length +1 = 3, and the ending line number is page number * page length =4):

Select * from table where rownum>=3 rownum =3 condition, so the first line is removed, the previous second line becomes the new first line (that is, this line number is not written dead, can be understood as dynamic), so on, until the last line, the condition is not satisfied, so even a piece of data can not be found.

To solve this problem, we just need to query the line number to generate a result set, and then from this result set, select the line number greater than the value we set. The correct way to write the pagination search above should be like this:

select id,col from(select rownum rn,u.* from table u) uawhere ua.rn between 3 and 4;

The above statement can also be optimized: although it cannot be used ">=","

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.