Understanding of rownum and rowid in Oracle

Rownum and rowid are all pseudo columns. However, rownum is a logical number and its value always starts from 1. The rounum of each row is not fixed. The rowid is a "physical" number. If the database file is not moved, the rowid of each row is generally fixed.

Summary of rownum usage in racle

For the rownum problem in Oracle, many documents do not support>, >=, =, between... and. The preceding symbols can only be used (<, <= ,! =), Not to mention>,> =, =, .. 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. rownum and rowid can be somewhat different. The following is an example.

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 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 always displays the records

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 reason is the same as above, 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.

In addition, you must note that rownum cannot use any base table name as the prefix.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12