Summary of rownum usage in Oracle

Guidance:ForOracle DatabaseOfRownumThe problem is that many materials are not supported>, >=, =, ...... And, can only use the above symbols (<, <= ,! =), Not to mention>, & gt; =, =, ...... And will prompt SQL syntax errors, but often cannot find a record, and it seems to be an inexplicable result, in fact, you should not be surprised to understand the meaning of this rownum pseudo column. It is also a pseudo column. The rownum and rowid can be somewhat different. In the following example, easy for everyone to understand and learn.

Assume that a table t1 (c1) has 20 records.

If select rownum, c1 from t1 where rownum <10 is used, as long as it is less than a number, the results can be easily agreed with the general understanding in terms of concept, and there should be no doubt.

If you use select rownum, c1 from t1 where rownum> 10 (if you write such a query statement, you should want to get the next 10 records in the table in your mind ), you will find that the displayed results will disappoint you. Maybe you will doubt that no one has deleted some records and then check the number of records. Is it still 20? Where is the problem?

First, understand the meaning of rownum. Because ROWNUM is a pseudo column added to the result set, that is, a column added after the result set is found (emphasize: a result set must be created first ). In short, rownum is the serial number that matches the condition result. It always starts from 1. Therefore, the result you select cannot be less than 1, and there are other values greater than 1. Therefore, you cannot expect the following result set:

11 aaaaaaaa

12 bbbbbbb

13 ccccccc ......

Rownum> 10 does not have a record. If the first row does not meet the requirements, the ROWNUM of the second row becomes 1, so there will never be a record that meets the conditions. Or you can understand it as follows:

ROWNUM is a sequence in which oracle databases read data from data files or buffers. If it obtains the first record, the rownum value is 1, the second record is 2, and so on. If you use>,> =, =, ...... And, because the rownum of the first record obtained from the buffer or data file is 1, it is deleted and then removed. However, its rownum is still 1 and deleted, and so on.

With the concept of rownum established from different aspects above, we can come to understand the several images using rownum:

1. select rownum, c1 from t1 where rownum! = 10 why is the first nine data records returned? It is the same as select rownum, c1 from tablename where rownum <10 returned result set?

Because after the result set is queried and 9th records are displayed, all subsequent records are displayed! = 10, or> = 10, so only the first nine records are displayed. It can also be understood that the value of rownum is 10 for the record after rownum is 9, because the condition is! = 10, so remove it. Then add the record, and the rownum is 10. If so, only the first nine records will be displayed.

2. why can't we find a record when rownum> 1, while rownum> 0 or rownum> = 1 is always displayed, because rownum is added after the queried result set, it always starts from 1

3. Why between 1 and 10 or between 0 and 10 can be found, but between 2 and 10 cannot be obtained. The same is true because rownum always starts from 1.

It can be seen from the above that it is wrong to discard the rownum = 1 record at any time, and it is indispensable in the result set. If rownum = 1 is missing, it cannot exist like a castle in the air, therefore, your rownum condition must include 1.

However, if you want to use rownum> 10, you need to use nested statements to convert Mr. rownum into and then query it.

Select *

From (selet rownum as rn, t1. * from a where ......)

Where rn> 10

This is usually the case when the result set is paged in the code.

In addition, both rowid and rownum are called pseudo columns, but they exist in different ways. rowid can be physical, indicating the unique location ID of the record in the tablespace, it is unique in DB. As long as the record has not been moved, the rowid remains unchanged. Compared with a table, rowid is similar to a common column in a table. Therefore, if rowid is used as a condition, rownum does not occur.

Note:

1. rownum cannot be prefixed with any base table name.

2. The rownum in the subquery must have an alias; otherwise, the record is not found because rownum is not a column in a table. If the alias cannot be found, you cannot know whether rownum is a subquery column or a primary query column.

3. query the rownum data in a certain range. What should I do? From the above, we can see that the rownum query condition for a value smaller than a certain value is true, rownum is regarded as false for query conditions greater than a certain value, but it can be converted to true indirectly. Subquery is required.

4. rownum and sorting. Subquery.

The usage of rownum in Oracle is described so much. I believe that through this study, we have a better understanding of rownum in Oracle.