The use of rownum In the result set sorting in Oracle, using lerownum

The use of rownum In the result set sorting in Oracle, using lerownum
Use of rownum in Oracle for sorting in result sets
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.

There is a table: tbrole
If you run the select r. * Statement, rownum from tbrole r where rownum> 10; ---- the results are often not displayed, but when you view the database, we can see that there are records in the database.

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.

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.

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, r. * from tbrole r where ...)
Where rn> 10

This is usually the case when the result set is paged in the code.

Oracle rownum is generated when records are extracted. It is prior to sorting. Therefore, you must use subqueries to sort records first..

ROWNUM value allocation is performed after the queried predicate is parsed, before any sorting and aggregation.

Therefore, if you want to query the corresponding records in tbrole by page, you do not need to execute the SQL statement like this.

Select * from
(Select r. role_id, r. role_name, r. status, to_char (r. create_time, 'yyyy-MM-DD HH24: MI: ss') create_time,
To_char (r. update_time, 'yyyy-MM-DD HH24: MI: ss') update_time, r. description, rownum rn from (select * from tbroleorder by create_time asc) r where 1 = 1
And rownum <= 20)
Where rn> = 1;

Instead:

Select * from
(Select r. role_id, r. role_name, r. status, to_char (r. create_time, 'yyyy-MM-DD HH24: MI: ss') create_time,
To_char (r. update_time, 'yyyy-MM-DD HH24: MI: ss') update_time, r. description, rownum rn from (select * from tbrole) r where 1 = 1
And rownum <= 20)
Where rn> = 1 order by create_time asc;

Execution sequence:

The FROM/WHERE clause is executed first.
ROWNUM is allocated to the rows output by the FROM/WHERE clause and increases by itself.
SELECT is applied.
Group by is applied.
HAVING is applied.
Order by is applied

Effect of ROWNUM on Performance
ROWNUM can avoid oracle sorting on disks. Rownum cannot avoid full table scanning, but it can avoid sorting the entire table data. After rownum is specified, the sorting operation can be easily completed in the memory.

Blog reference: http://blog.csdn.net/c04s31602/article/details/5105161

How to use "rownum" to retrieve the specified records in the result set after Oracle sorting

Select formatted_entry from (select row_number () over (order by to_number (product_code), sample_number) rn, a. * from sample a where ...)
Where rn = I _Batch_No;

Why not?

Rownum in oracle

Except for the index organization table (IOT), the Heap tables in other table storage are unordered, And the queried data is not necessarily in the order of receipt. Remember
Therefore, it is best to add order by with rownum.
Solution:
Query:
Select * from table_a where
Rowid in (select rowid from table_a where rownum <= 2000 order by id)
Delete:
Delete from table_a where
Rowid in (select rowid from table_a where rownum <= 2000 order by id)
Sort by id here. Because IDs are indexed and the index itself is ordered, order by id will not cause performance problems.