On ROWID and RowNum in Oracle

Summary

Students who have just come into contact with Oracle may often be confused by the two words of ROWID and rownum, figuring out that these two guys are very helpful to us in writing SQL, so let's just talk about the difference between them.

Comparison

rowID and rownum are all pseudo-columns in Oracle, but they still have an essential difference:

ROWID: Is the physical address that is used to locate the data in the data table, which is unique and does not change.

RowNum: is a logical number assigned to each row based on the result set of the query, the query results are different, rownum is naturally different.

For the same record, the query conditions are different, rownum will be different, but the ROWID will not change.

Example: Querying all employees of a company

Select rowID, RowNum, empno, ename from EMP;

Example: Query company employee name contains ' S '

Select rowID, RowNum, empno, ename from emp where ename like '%s% ';

Careful classmate is not difficult to find, for a row of records, rowID unchanged, rownum changed. For example: Employees whose name is ' SMITH '.

Trap

1. rownum only supports symbol number <, <=,! =, not supported ;, >=, =, Between...and

SELECT * from emp where rownum > 10-no results

SELECT * FROM  (select RowNum row_num, e.* from emp e) emp_temp where Emp_temp.row_num > 10;--can get 10 bit later record

Explain this: Because RowNum is a pseudo-column for the result set, that is, after finding the result set and then adding a column (emphasizing: to have a result set). Simply put, rownum is the serial number that matches the conditional result, and it always starts at 1, so you can't choose a result without 1 and a value greater than 1.

With the knowledge above, it is not difficult to explain the following phenomena:

① Why does the select * from emp where rownum! = 10 always return the first 9 records?

Because after the query to the result set, the 9th record is displayed, and the subsequent record is! = 10.

② why rownum > 1 can't find a record, and rownum > 0 or rownum >= 1 always shows all records?

Because RowNum is added after the query to the result set, it always starts at 1.

③ why between 1 and 10 or between 0 and 10 can find results, and between 2 and 10 get no results?

The reason is the same, less rownum=1 is like castles in the castle can not exist.

Application

What's the use of these two buddies in a bunch of crap? Hey, don't worry, it's on the right dish ...

Question: Get the top three employees in the payroll

Analysis: If it is SQL Server, we can use the TOP keyword, Oracle we use rownum also can solve

SELECT * FROM   [select * from emp ORDER BY sal Desc]  where rownum <= 3  ORDER by rownum ASC