The difference between rownum and ROWID in Oracle

The difference between rownum and rowID is summed up:

RowNum and rowID are pseudo-columns, but the two are fundamentally different.

RowNum is to assign a logical number to each row based on the results of the SQL query, so your SQL will cause the final rownum to be different.

However, ROWID is a physical structure that has a unique physical record when each record is insert into the database.

• RowNum Paging:

using rownum for Oracle paging

• rowID, the primary key is still unique , only to remove the name and age of repeat students:

Select A.rowid,a.* from Student a        where A.rowid not in              (SELECT MAX (b.rowid) from student b where a.sname = B.sname and a.sage = B.sage);

The above SQL statement is difficult to understand because the source and target are cross-queried.

Chinese interpretation (from the inner Select to start understanding):

1. From the Studneta table, find and STUDENTB the same name and the same age students in the table.

2. Find out the biggest rowid of the students with the same name.

3. From the Studenta table, look for those students who are not in the maximum rowid range.

The really used de-redo statements are:

DELETE from Student a        where A.rowid not in              (SELECT MAX (b.rowid) from student b WHERE a.sname = B.sname and A.sage = B.sage);

More detailed references for ROWID:

Efficient way to remove duplicate data from an Oracle database

　　The difference between rownum and ROWID in Oracle

The difference between rownum and ROWID in Oracle