Detailed description of Oracle rownum pseudo Columns

[Application Scenario] many colleagues have asked me about pseudo columns. I mainly want to add some serial numbers = when exporting data. In fact, pseudo columns have many functions.

Official reference: Oracle Database

SQL Language Reference

11g Release 2 (11.2)

E17118-04

Rowid pseudo column & rownum pseudo Column

The following content is organized on the Internet:

Both rowid and rownum are called pseudo columns, but they exist in different ways. rowid can be said to be physical, indicating the unique location ID of the record in the tablespace, which is unique in the DB. As long as the record has not been moved, rowid remains unchanged (if the table is shrink or move rowid, it will change ). Compared with a table, rowid is similar to a common column in a table. Therefore, if rowid is used as a condition, rownum does not occur. Rownum cannot be prefixed with any base table name.

For rownum, it is the number of the row returned from the query that is sequentially allocated by the Oracle system. The first row is allocated 1, and the second row is 2, this pseudo field can be used to limit the total number of rows returned by the query, and rownum cannot be prefixed with any table name.

Example:
Example Table: Student (student) table with the following structure:

Create Table student (ID char (6), name varchar2 (100 ));

Insert into student

Values ('000000', 'zhang yi ');

Insert into student

Values ('20140901', 'wang 2 ');

Insert into student

Values ('20140901', 'l3 li ');

Insert into student

Values ('20140901', 'zhao si ');

Commit;

1. rownum for query conditions equal to a certain value
If you want to find the information of the first student in the student table, you can use rownum = 1 as the condition. However, if you want to find the second student information in the student table, you cannot find the data using rownum = 2. Because rownum starts from 1, but the natural numbers above 1 are regarded as false when rownum is equal to or equal to the true value. Therefore, rownum = N (The Natural Number of n> 1) cannot be found ).

SQL> select rownum, ID, name from student where rownum = 1;

Rownum ID name

-----------------------------------------------------

1 200001 Zhang Yi

It can be used to limit the number of returned records to avoid errors, such as implicit cursor.

SQL> select rownum, ID, name from student where rownum = 2;

SQL> select rownum, ID, name from student where rownum = 1; (it can be used to limit the number of returned records to ensure no error, for example, implicit cursor)

SQL> select rownum, ID, name from student where rownum = 1;

Rownum ID name

-------------------------------------------------------------------

1 200001 Zhang Yi

SQL> select rownum, ID, name from student where rownum = 2;

Rownum ID name

-------------------------------------------------------------------

2 rownum for query conditions greater than a certain value
If you want to find the record from the second row, when rownum> 2 is used, the record cannot be found because rownum is a pseudo column always starting from 1, oracle considers that the condition rownum> N (Natural Number of n> 1) is still not true, so records cannot be found.

SQL> select rownum, ID, name from student where rownum> 2;

Select rownum, ID, name from student where rownum> 2;

Rownum ID name

-------------------------------------------------------------------
Then how can we find the record after the second row. You can use the following subquery method to solve the problem. Note that the rownum in the subquery must have an alias; otherwise, the record is not found because rownum is not a column of a table. If the alias cannot be found, you cannot know whether rownum is a subquery column or a primary query column.

SQL> select * from (select rownum No, ID, name from student) where no> 2;

No ID name

----------------------------------------------------------------------

3 200003 Li San

4 200004 Zhao Si

SQL> select * from (select rownum, ID, name from student) Where rownum> 2;

Select * from (select rownum No, ID, name from student) where no> 2;

No ID name

-------------------------------------------------------------------

3 200003 Li San 4 200004 Zhao Si

SQL> select * from (select rownum, ID, name from student) Where rownum> 2;

Rownum ID name

-------------------------------------------------------------------
3. rownum for query conditions smaller than a certain value
If you want to find the previous record of the third record, use rownum <3 to get two records. Obviously, rownum considers the condition of rownum <n (Natural Number of n> 1) as true, so records can be found.

SQL> select rownum, ID, name from student where rownum <3;

Rownum ID name

-------------------------------------------------------

1 200001 Zhang Yi

2 200002 Wang 2 SQL> select rownum, ID, name from student where rownum <3;

Rownum ID name

-------------------------------------------------------------------

1 200001 Zhang Yi

2 200002 Wang 'er
To sum up the preceding situations, you may need to query rownum data in a certain range. What should you do? We can see that the rownum query condition for a value smaller than a certain value is true, rownum is regarded as false for query conditions greater than a certain value, but it can be converted to true indirectly. Subquery is required. For example, to query the data of rownum between the second row and the third row, including the data of the second row and the third row, we can only write the following statement to first let it return the record rows smaller than or equal to three, then, in the primary query, it is judged that the alias column of the new rownum is greater than or equal to two record rows. However, such operations will affect the speed in the big data set.

SQL> select * from (select rownum No, ID, name from student where rownum <= 3) where no> = 2;

No ID name

-------------------------------------------

2 200002 Wang 'er

3 200003 Li San SQL> select * from (select rownum No, ID, name from student where rownum <= 3) where no> = 2;

No ID name

-------------------------------------------------------------------

2 200002 Wang 2 3 200003 Li San

4. rownum and sorting
In Oracle, rownum is the sequence number generated when data is retrieved. Therefore, you must pay attention to the specified rowmun rows of data to be sorted.

SQL> select rownum, ID, name from student order by name;

Rownum ID name

------------------------------------------------------

3 200003 Li San

2 200002 Wang 'er

1 200001 Zhang Yi

4 200004 Zhao Si SQL> select rownum, ID, name from student order by name;

Rownum ID name

-------------------------------------------------------------------

3 200003 Li San

2 200002 Wang 'er

1 200001 Zhang Yi

4 200004 Zhao Si
It can be seen that rownum is not the serial number generated by the name column. The system assigns the number of the record row according to the sequence in which the record is inserted, and the rowid is also allocated sequentially. To solve this problem, you must use the subquery

SQLCode

SQL> select rownum, ID, name from (select * from student order by name );

Rownum ID name

-------------------------------------------------------------------

1 200003 Li San

2 200002 Wang 'er

3 200001 Zhang Yi

4 200004 Zhao Si SQL> select rownum, ID, name from (select * from student order by name );

Rownum ID name

-------------------------------------------------------------------

1 200003 Li San 2 200002 Wang 'er

3 200001 Zhang Yi 4 200004 Zhao Si

In this way, the sequence is sorted by name, and the correct sequence number (from small to large) is marked with rownum)

For a table with millions of records, you must display the table by page on the JSP page. You can use rownum to display the table by page. The following is a specific method (20 records are displayed on each page ):

Select * From tabname where rownum <20 order by name

Select * From tabname where rownum <20 order by name

However, it was found that Oracle could not execute as needed, but first took 20 records at will, and then order by, and then consulted Oracle, saying that rownum is indeed like this, if you want to use this function, you can use subqueries to sort data first and then rownum. The method is as follows:

Select * from (select * From tabname order by name) Where rownum <20

Select * from (select * From tabname order by name) Where rownum <20

However, the efficiency will be much lower.
After the experiment, you only need to add a primary key or index to the order by field to let oracle first sort by this field, and then rownum; the method remains unchanged:

Select * From tabname where rownum <20 order by name

Select * From tabname where rownum <20 order by name

5. Obtain the nth row in a column

Select column_name from

(Select table_name. *, dense_rank () over (order by column DESC) rank from table_name)

Where Rank = & N;

Select column_name from

(Select table_name. *, dense_rank () over (order by column DESC) rank from table_name)

Where Rank = & N;

6. If you want to return the first five records:

Select * From tablename where rownum <6; (rownum <= 5 or rownum! = 6)

Select * From tablename where rownum <6; (or rownum <= 5 or rownum! = 6)

7. If you want to return records 5-9:

Select * From tablename

Where...

And rownum <10

Minus

Select * From tablename

Where...

And rownum <5

Order by name

Select * From tablenamewhere... And rownum <10 minusselect * From tablenamewhere...

And rownum <5

Order by name

After the results are selected, the results are sorted by name. (Select first and then sort)

In addition, this method is faster:

Select * from (

Select rownum R, a from yourtable

Where rownum <= 20

Order by name)

Where R> 10

Select * from (select rownum R, a from yourtablewhere rownum <= 20 order by name)

Where R> 10

In this way, the 11-20 records are retrieved! (Select, sort, and then select)

Note: minus indicates a difference set from the perspective of a set, for example:
Select ID from Table1
Minus
Select ID from Table2
Indicates that the ID is in Table 1 and is not in table 2.

Note:
1. Only the above symbols can be used (<, <= ,! = ).
2. Select * From tablename where rownum! = 10; the first nine records are returned.
3. It cannot be used:>, >=, =, between... and. Since rownum is a pseudo column that always starts from 1, Oracle does not think this condition is true. This cannot be used because records cannot be found.
4. to sort and then select, select nested: the outer layer of the inner layer is selected.
5. rownum is generated along with the result set. Once generated, it will not change. At the same time, the generated results will be incrementing in sequence. If there is no 1, there will never be 2!
6. rownum is a pseudo column generated when the query set is generated. If the where condition contains the rownum condition, then:

6.1: if the condition is a constant:
Only rownum = 1, <= a natural number greater than 1, = A number greater than 1 has no results, and a number greater than one has no results.
That is, when a rownum does not meet the condition, the query ends. This is stop key!

6.2: when the judgment value is not a constant
If the condition is = var, the condition is met only when VaR is 1. In this case, the Stop key does not exist. Full scan is required to determine the data that meets other where conditions, select a row before selecting rows with rownum = 2.