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
Rowid is usually fixed.

 

Summary of rownum usage in racle

For Oracle rownum
The problem is that many documents do not support >>=, =, between... and, and only the above symbols can be used (<, <= ,! =), Not to mention>,> =, =, .. and will prompt SQL syntax errors, but often cannot find a record, it seems to be an inexplicable result, in fact, as long as you understand this
The meaning of the rownum pseudo column should not be surprising. It is also a pseudo column. The rownum and rowid can be somewhat different. The following example shows

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, and you may doubt that no one has deleted some records, and then check the number of records.
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 ). Simply put
Rownum is the serial number of the matching 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 understand how to use rownum.
Several images

 

1. Select rownum, C1 from T1 where rownum! = 10 why is the first nine data records returned? It corresponds
Select rownum, C1 from tablename where rownum <10 returns the same 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 record after rownum is 9
Rownum is 10, 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 I find a record when rownum> 1, while rownum
> 0 or rownum> = 1, but all records are displayed.

Because rownum is added after the queried result set, it always starts from 1.

 

3. Why between 1 and 10 or
0 and 10 can find the result, but between 2 and 10 cannot get the result.

The reason is the same as above, because rownum is always from 1
Start

 

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

 

However, if you want to use rownum> 10, you need to use nested statements to set rownum
Mr Cheng, and then query him.

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, rowid and rownum
Although they are all called pseudo columns, they exist in different ways. rowid can be said to be physical, indicating the unique location ID of the record in the tablespace, which is unique in the DB. As long as the record has not been moved, the rowid remains unchanged. Compared with the table, rowid is similar to a common column in the table.
If rowid is a condition, no rownum occurs.

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