The difference between rownum understanding and rowID in Oracle

Rownum,rowid are called pseudo-columns. However, RowNum is a logical number, and its value always starts at 1, and the rounum of each row is not fixed. And rowID is the "physical" number. If the database file is not moved, the rowid of each row is generally fixed.

Summary of rownum usage in Oracle

For Oracle's rownum problem, a lot of data are said not to support >,>=,=,between...and, only with the above symbols (<, <=,! =), not to say with >,>=,=,between. and will prompt SQL syntax error, but often do not find a record, but also appear to be inexplicable results, in fact, you just understand the meaning of this rownum pseudo-column should not be surprised, the same pseudo-column, rownum and ROWID can be somewhat different, the following examples illustrate

Suppose a table T1 (C1) has 20 records

If you use the Select Rownum,c1 from t1 where RowNum < 10, as long as it is less than the number, the results can easily be found with the general understanding in the conceptual agreement, there should be no doubt.

But if you use select Rownum,c1 from t1 where rownum > 10 (If you write such a query, it should be in your mind to get the next 10 records in your table), you will find that the results you have shown will disappoint you. You may also wonder if someone deleted some records and then looked at the number of records, still 20? So where is the problem?

Understand the meaning of rownum first. Because RowNum is a pseudo-column added to the result set, the result set is followed by a column (emphasis: The result set first). Simply put, the rownum is the serial number that matches the conditional result. It always starts at 1. So your chosen result cannot be no more than 1, and there are other values greater than 1 . So you can't expect to get the following result set:

Aaaaaaaa

bbbbbbb

Ccccccc

.................

RowNum >10 no record, because the first one does not meet the removal, the second of the rownum 1, so never meet the conditions of the record. Or you can understand this:

RowNum is a sequence that is the order in which the Oracle database reads data from a data file or buffer. It gets the first record then the rownum value is 1, the second one is 2, and so on. If you use >,>=,=,between...and these conditions, because the rownum of the first record obtained from the buffer or data file is 1, it is deleted, then the bar is removed, but its rownum is still 1, deleted, and so on, without data.

With the concept of rownum that has been built up from different aspects, we can come to know some of the current images using rownum

1. Select rownum,c1 from t1 where rownum! = 10 is the first 9 data returned? Is it the same as the result set returned by the Select Rownum,c1 from tablename where RowNum < 10?

Because after the 9th record is displayed after the query to the result set, the subsequent records are also! = 10, or >=10, so only the previous 9 records are displayed. It can also be understood that rownum for the record of 9 after the rownum for 10, because the condition is!=10, so remove, then record to fill, RowNum is 10, also removed, if down will only show the preceding 9 records

2. Why RowNum >1 when a record is not found, and rownum >0 or rownum >=1 always show All records

Because RowNum is added after the query to the result set, it always starts at 1

3. Why between 1 and 10 or between 0 and 10 can find results, but with between 2 and 10 without results

Same reason, because rownum always starts from 1.

From the above can be seen, any time want to RowNum = 1 This record abandoned is not right, it is indispensable in the result set, less rownum=1 like castles in the castle can not exist, so your rownum conditions to include 1

But if you want to use RowNum > 10, then use nested statements, put Mr. RowNum, and then query him.

SELECT *

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

where RN >10

Paging through the result set in general code is the way to do it.

In addition: both ROWID and rownum are called pseudo-columns, but they exist in a different way, ROWID can be said to be physically present, representing the unique location ID recorded in the tablespace, unique in the DB. As long as the record has not been moved, ROWID is unchanged. rowID is like a normal column in a table relative to a table, so there will be no rownum that happens if ROWID is the condition .

Also note:rownum cannot be prefixed with the name of any base table .

The difference between rownum understanding and rowID in Oracle