The difference between rownum and ROWID in Oracle

RowNum and rowID are pseudo-columns, but the root of the two is different, rownum is to assign a logical number to each row based on the results of the SQL query, so your SQL will result in a different final rownum, but rowID is physically structured, When each record is insert into the database, there is a unique physical record,
For example Aaamgzaaeaaaaagaab 7499 ALLEN salesman 7698 1981/2/20 1600.00 300.00 30
The Aaamgzaaeaaaaagaab physical location here corresponds to this record, which does not change as SQL changes.
Therefore, this results in a different usage scenario, usually when SQL paging or when looking for a range of records, we use RowNum.
1, RowNum
For example:
Find records in the range 2 to 10 (this includes 2 and 10 records)
SELECT *
From (select RowNum rn, a.* from EMP a) t
where T.rn between 2 and 10;

Find the top three records
SELECT * from emp a where rownum < 3; It is important to note that the range to be searched directly with rownum must contain 1, since RowNum is recorded starting from 1. Of course, you can find the rownum and put it in a virtual table as the field of this virtual table and then query according to the criteria.
For example:
SELECT *
From (select RowNum rn, a.* from EMP a) t
where T.rn > 2; that's it.

2, rowID
We often use him when we are dealing with repeating records in a table, but you can also use a very primitive method, which is to lead the data in a table with duplicate records into another table, and then rewind it back.
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; Adding data from a staging table back to the original table but if the Stu table data is millions or larger tens, then this method is obviously unwise, so we can deal with ROWID, ROWID is unique, query efficiency is very high,
For example, the name of the student table will be repeated, but the student's number is not repeated, if we want to delete the student table in the name of repeating the student only the largest students record, how to do?
Delete from Stu a
where rowID not in (select Max (ROWID)
From Stu B
where a.name = B.name
and A.stno < B.stno);

The difference between rownum and ROWID in Oracle