Oracle Pseudo-Column

ROWNUM

ROWNUM: Represents a row number, which is actually a column, but this column is a pseudo-column that can appear in each table.

Example: On the Query Employee table, add ROWNUM

SELECT  from EMP;

From the running, the ROWNUM itself is in the form of automatic numbering.

Suppose you want to show only the first 5 records. Condition: rownum<=5

SELECT rownum,empno,ename,job,sal,hiredate  from WHERE ROWNUM<=5;

If you want to do the intermediate interception operation, you can only use sub-query, for example, now assume that each page shows 5, the 2nd page should show 6~10, then for the database operation, it should first query the 1~10 bar, and then in the results of the query to remove the last 5.

SELECT *  from (SELECT  ROWNUM rn,empno,ename,job,sal,hiredate    fromWHERE ROWNUM<=  ThetempWHEREtemp. rn>5;

ROWID

ROWID: The globally unique address of the row in the database, ROWID uses the physical address of the Base64 encoded line, and the encoded characters contain a-Z, A-Z, 0-9, +, and/

ROWID consists of four parts: ooooooofffbbbbbbrrr:

OOOOOO: Data Object Number (6-bit display) FFF: Correlation data File Number (3-bit display) BBBBBB: Block Number (6-bit display) RRR: Data Block Row number (3-bit display)

rowID can be used to delete duplicate data in a data table

Select rowid, emp.* from EMP;

Add Duplicate data

Insert  into Select *  from where empno=7369;

The newly added data is rowid larger than the previous data rowid, so you can use the Min function to select the ROWID of the non-repeating data

Select min  from Group  by Empno, Ename,job,mgr,hiredate,sal,comm,deptno

Delete duplicate data

Delete  from where  not inch (  Selectminfromgroup  by empno, Ename,job,mgr, HIREDATE,SAL,COMM,DEPTNO);

View all data again, duplicate data deleted

　

• 　　RowNum primarily for server paging
• rowID can be used to delete duplicate data

Oracle Pseudo-Column