Introduction to Oracle's ROWID and rownum

Summary

Students who have just come in contact with Oracle may often be confused by the two words rowid and rownum, figuring out how helpful these two guys are for us to write SQL, and I'd like to briefly talk about the difference between them.

Comparison

Both rowID and rownum are pseudo columns in Oracle, but they are fundamentally different:

ROWID: is a physical address that locates the data in a datasheet and is unique and does not change.

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

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

Example: Querying all employees of the 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 students are not difficult to find, for a row of records, ROWID has not changed, rownum changed. For example: the employee whose name is ' SMITH '.

Trap

1. rownum only supports symbols <, <=,!=, 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;--You can get 10-bit records later

Explanation: Because RowNum is a pseudo column that adds a result set, that is, the result set is first followed by a column (emphasis: to have a result set). Simply put, rownum is the serial number that matches the conditional result, it always starts from 1, so the result you choose is not 1, but a value greater than 1.

With the above knowledge, we can not be difficult to explain the following phenomena:

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

Because after the result set is queried, the 9th record is displayed, followed by!= 10.

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

Since 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, while between 2 and 10 do not get results?

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

Application

What is the use of these two brothers in a basket of nonsense? Hey, don't worry, the following is on the vegetables ...

Question: Get the top three employees

Analysis: If the SQL Server is good, we can use the top keyword, Oracle, we can use rownum to solve

 SELECT * FROM
   (SELECT * from EMP-desc)
  where rownum <= 3 order by
  rownum ASC

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/