Oracle RowNum Problem __oracle

Today, colleagues have encountered a problem because of the rownum problem.

Next to an article:

For Oracle's rownum problem, a lot of information said that do not support >,>=,=,between......and, only use the above symbols (<, & lt;=,. =), not that the >,>=,=,between......and will prompt the SQL syntax error, but often is not able to find a record, there will appear to be inexplicable results, in fact, you just understand the meaning of this rownum pseudo column should not be surprised, is also pseudo column , RowNum and rowid can be somewhat different, as illustrated in the following example:

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 detected results can easily be conceptually agreed with the general understanding, there should be no doubt.

If you use the Select Rownum,c1 from t1 where rownum > 10 (If you write such a query, you should want the following 10 records in your mind), you will find that the results shown will disappoint you, You may also suspect that no one has deleted some of the records, and then check the number of records, still 20. The problem is where it is.

Understand the meaning of rownum first. Because RowNum is a pseudo column that adds a result set, that is, the result set is first followed by a column (emphasis: The result set is first). Simply put, the rownum is the serial number that matches the conditional result. It's always lined up from 1. So your chosen result cannot be 1, and there are other values greater than 1. So you have no way to expect the following result set:

One aaaaaaaa

bbbbbbb

CCCCCCC .....

RowNum >10 no record, because the first is not satisfied with the words, the second rownum into 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 is 2, and so on. If you use >,>=,=,between......and these conditions, because the first record obtained from the buffer or data file is rownum to 1, it is deleted, then the strip is removed, but its rownum or 1 is deleted, and so on, then there is no data.

With the above concept of rownum from different aspects, we can come to understand the use of several rownum

1. Select rownum,c1 from T1 where rownum. = 10 Why is the first 9 data returned? It is the same as the result set returned by select ROWNUM,C1 from TableName where RowNum < 10.

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

2. Why RowNum >1 when a record is not found, and rownum >0 or rownum >=1 is always showing the record because rownum is in the query to the result set added after, it always starts from 1

3. Why between 1 and 10 or between 0 and 10 can find results, and between 2 and 10 do not get the same result as the same as rownum always start at 1

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

But if you want to use the RowNum > 10 of this condition, you will use the nested statement, rownum, and then query him.

SELECT *

From [Selet rownum as rn,t1.* from a where ... ）

where RN >10

This is the way in which the result set is paginated in general code.

In addition: rowID and rownum are known as pseudo columns, but they exist in a different way, ROWID can be said to be physically present, indicating that the unique location ID recorded in the table space is unique in db. As long as the records have not been moved, the ROWID is unchanged. rowID is like a general column in a table relative to a table, so rowID doesn't have rownum that happens.

RowNum and Sorting:
Sql> Select RowNum, id,name from student order by name;
RowNum ID NAME
---------- ------ ---------------------------------------------------
3 200003 Lie triple systems
2 200002 King Two
1 200001 Sheets A
4 200004 Zhao Si
As you can see, rownum is not the serial number generated by the Name column. The system is the number of records in the order in which they were inserted, and ROWID is also assigned sequentially. To solve this problem, you must use a subquery
Sql> Select RowNum, Id,name from (SELECT * to student order by name);
RowNum ID NAME
---------- ------ ---------------------------------------------------
1 200003 Lie triple systems
2 200002 King Two
3 200001 Sheets A
4 200004 Zhao Si
This is sorted by name, and the correct number is marked with rownum (from small to large)