Use of rownum in Oracle for sorting in result sets

Use of rownum in Oracle for sorting in result sets

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's rownum is generated after the record is extracted. It is prior to the sorting operation. Therefore, you must use the subquery 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 tbrole order 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.