RowNum Usage Alert in Oracle

　　Debugging the code today and discovering a bug that uses RowNum's Between......and usage in Oracle in a paged query is summarized as follows:

Reference: http://blog.csdn.net/lg312200538/article/details/4587455

For Oracle's rownum problem, a lot of data are said not to support >,>=,=,between......and, only with the following symbols (<, <=,!). =), not to say that the use of >,>=,=,between......and will prompt the SQL syntax error, but often can 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 who 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, which 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 Why 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 query to the result set, after the 9th record is displayed, then the records are also! = 10, or >=10, so only the previous 9 records are displayed. It can also be understood that RowNum is 9 records after the rownum of 10, due to the condition! =10, so remove, then record, RowNum is 10, also removed, if down will only show the previous 9 records.　　

2. Why can't I find a record when RowNum >1, and rownum >0 or rownum >=1 always show all the records?
　　Because RowNum is added after a query to the result set, it always starts at 1, and the rownum is incremented after the record that satisfies the condition is obtained.　　

3. Why is between 1 and 10 or between 0 and 10 able to find results, with between 2 and 10 without results?
　　The same reason, because rownum always starting from 1, the satisfaction of the condition is incremented, the condition is not satisfied rownum, then never satisfied. 　　

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 to 1, but if you want to use RowNum > 10 This condition Words will use nested statements, the rownum into a sub-query, 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 rowID will not have rownum those conditions.

It is also important to note that:

1, rownum cannot be prefixed with the name of any base table.

2, the rownum in the subquery must have an alias, or the record will not be detected, because rownum is not a column of a table, if you do not alias, you can not know whether RowNum is a subquery column or the main query column

3, query rownum in a certain interval of data, then how to do? As can be seen from the rownum to a value less than a query condition is considered true, rownum for a value greater than a query condition is directly considered false, but can indirectly let it be considered true. Then you must use the subquery.

4,rownum and sorting. Sub-query. Many implementation details need to be validated in the future.

RowNum Usage Alert in Oracle