The use of Rownum,rowid in Oracle

1.ROWNUM Use--top-n Analysis

Using the result set returned by the SELECT statement, you can use pseudo-column rownum if you want to query the top N records by specific criteria.

RowNum is a pseudo-column that adds to the result set, which is the result set followed by a column (emphasis: The result set first). Simply put, rownum is the serial number that matches the conditional result. It always starts at 1.

When using RowNum, you can only use the <, <=,! = symbols.

Example:

Student (student) table, the table structure is:

ID Char (6)--School number

Name VARCHAR2 (10)--Name

--Build a table

CREATE TABLE Student (ID char (6), name VARCHAR2 (10));

--Add Test records

INSERT into student values (' 200001 ', ' Zhang Yi ');

INSERT into student values (' 200002 ', ' King Two ');

INSERT into student values (' 200003 ', ' lie Triple ');

INSERT into student values (' 200004 ', ' Zhao Si ');

Commit

--Test

Sql> select * from student;

ID NAME

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

200001 Sheets A

200002 Wang ER

200003 lie triple

200004 Zhao Si

⑴rownum for a query condition equal to a value

If you want to find information about the first student in the student table, you can use Rownum=1 as a condition. But to find the information of the second student in the Student's table, the data was not found using rownum=2 results. Because the rownum are all starting from 1, but more than 1 of the natural number in rownum do equal to the judgment is considered false conditions, so can not find rownum = N (n>1 natural number).

Sql> Select Rownum,id,name from student where rownum=1;

ROWNUM ID NAME

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

1 200001 Zhang One

Sql> Select Rownum,id,name from student where rownum = 2;

Row not selected

⑵rownum for a query condition that is greater than a value

If you want to find records from the second row records, when using Rownum>2 is not a record, because rownum is always a pseudo-column starting from 1, Oracle thinks rownum> N (natural number of n>1) This condition is still not established, So I can't find the records.

Sql> Select Rownum,id,name from student where RowNum >2;

Row not selected

So how do you find the next line of records? You can use the subquery method to resolve. Note that the rownum in a subquery must have an alias, or the record will not be detected, because rownum is not a column of a table, and if you cannot alias it, you cannot know whether rownum is a column of a subquery or a column of a primary query.

Sql>select * FROM (select RowNum No, id,name from student) where no>2;

NO ID NAME

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

3 200003 Lie triple

4 200004 Zhao Si

Sql> SELECT * FROM (select Rownum,id,name from student) where rownum>2;

Row not selected

⑶rownum for query conditions that are less than a certain value

If you want to find a third record of previous records, you can get two records when using Rownum<3. Obviously rownum for rownum<n (the natural number of n>1) is believed to be established, so can find records.

Sql> Select Rownum,id,name from student where RowNum <3;

ROWNUM ID NAME

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

1 200001 Zhang One

2 200002 Wang ER

In a number of cases, may sometimes need to query rownum in a certain interval of data, you can see that rownum is less than a value of the query condition is 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. For example, to query rownum data between the second row and the third row, including the second row and the third row of data, we can write the following statement, first let it return a record row of less than or equal to 3, and then in the main query, the new rownum column is determined to be greater than or equal to 2 of the record row. However, such operations can affect speed in a large 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 Lie triple

⑷rownum and Sorting

RowNum in Oracle is the ordinal number generated when fetching data, so it is important to be aware of specifying rowmun rows of data for the specified sorted data.

Sql> Select RowNum, id,name from student order by name;

ROWNUM ID NAME

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

3 200003 Lie triple

2 200002 Wang ER

1 200001 Zhang One

4 200004 Zhao Si

As you can see, rownum is not the ordinal number generated by the Name column. The system is the number of records in the order in which records are inserted, and ROWID is assigned sequentially. To solve this problem, you must use a subquery

Sql> Select RowNum, Id,name from (SELECT * from Student order by name);

ROWNUM ID NAME

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

1 200003 Lie triple

2 200002 Wang ER

3 200001 Zhang One

4 200004 Zhao Si

This will be sorted by name, and the correct sequence number (from small to large) is marked with rownum. Order BY name If name is a primary key or has an index, the rownum of the query is exactly in the order of the ....

Sql> ALTER TABLE student add constraint Pk_stu primary key (name);

Sql> Select RowNum, id,name from student order by name;

ROWNUM ID NAME

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

1 200003 Lie triple

2 200002 Wang ER

3 200001 Zhang One

4 200004 Zhao Si

2. Use of rowID--Quickly delete duplicate records

rowID is the detailed address of the data, the location of the specific data of a row can be quickly located by Rowid,oracle.

rowID can be divided into two kinds of physical rowid and logic rowid. The ROWID in a normal table is a physical rowid, and the ROWID of an indexed organization table (IOT) is a logical rowid.

You can use rowID to quickly delete duplicate records when there is a large amount of duplicate data in the table.

Example:

--Build Table TBL

Sql> CREATE TABLE Stu (no number,name varchar2, sex char (2));

--Add Test records

sql> INSERT into Stu values (1, ' ab ', ' Male ');

sql> INSERT into Stu values (1, ' BB ', ' female ');

sql> INSERT into Stu values (1, ' ab ', ' Male ');

sql> INSERT into Stu values (1, ' ab ', ' Male ');

sql>commit;

There are a lot of ways to delete duplicate records, listing two types.

⑴ by creating a temporary table

You can pivot the data into a temporary table, delete the data from the original table, and then import the data back to the original table, with the following SQL statement:

Sql>create table stu_tmp as select distinct* from Stu;

sql>truncate table sut; Clear table Record

Sql>insert to Stu Select * from Stu_tmp; Add data from a staging table back to the original table

This method can achieve the requirements, but obviously, for a tens record of the table, this method is very slow, in the production system, this will bring the system a lot of overhead, not the line.

⑵ using ROWID to combine Max or min functions

Use ROWID to quickly uniquely determine duplicate rows in conjunction with the Max or Min function to implement the deletion of duplicate rows.

Sql>delete from Stu a where rowid not in (select Max (B.rowid) from Stu B where a.no=b.no and a.name = B.name and A.sex                                          = B.sex); Here max can also use min

Or use the following statement

Sql>delete from Stu a where rowID < (select Max (B.rowid) from Stu B where a.no=b.no and a.name = b.name and A.sex =       B.sex); If you change Max to Min, you need to change "<" to ">" in the previous where clause.

The idea is basically the same as the above method, but the group by is used to reduce the dominant comparison condition and improve the efficiency.

Sql>delete from Stu where rowID not in (select Max (ROWID) from Stu T Group by t.no, T.name, t.sex);

Think: If the Stu table only determines any row of data (1, ' ab ', ' Male '), the Sex field is updated to "female", How to do?

Sql>update Stu set sex= ' female ' where rowid= (select min (rowid) from Stu where no=1 and Name= ' ab ' and sex= ' male ');

The use of Rownum,rowid in Oracle