Skip to content

Rowid vs Rownum vs Row_Number() in oracle.

Learn the about the logical columns that are not exist in the table, but how can be used to select the data from the table, with explanation and example queries.

Rowid, Rownum are the Pseudo columns in oracle used to select the data from tables.

Rowid

  • ROWID is a pseudo column in a table which store and return row address in HEXADECIMAL format with database tables.
  • ROWID is the permanent unique identifiers for each row in the database.
  • ROWID consists of 18 character string with the format. BBBBBBBBB.RRRR.FFFF Where B is Block, R is Row, F is FIle.

Below is the example query to distinguish rowid address.

Example query:

SELECT rowid,empno FROM EMP1;

Output:

ROWIDEMPNO
AADZmhABAAAAckoAAA1
AADZmhABAAAAckoAAB 2
AADZmhABAAAAckoAAC3
AADZmhABAAAAckoAAD4
AADZmhABAAAAckoAAE5
AADZmhABAAAAckoAAF6
AADZmhABAAAAckoAAG 7
AADZmhABAAAAckoAAH 8
AADZmhABAAAAckoAAI9
AADZmhABAAAAckoAAJ10
AADZmhABAAAAckoAAK11
AADZmhABAAAAckoAAL 12
AADZmhABAAAAckoAAM 13
AADZmhABAAAAckoAAM 14

NOTES

  1. ROWID is representative of the allocation of physical memory.
  2. Oracle automatically generates a unique ROWID at the time of insertion of a row.
  3. ROWID is the permanent identity or address of a row which one can easily access(retrieve data from a row) the rows from the table.
  4. A user can access a row quickly and easily using its row ID.
  5. ROWID can also be used to delete normal records and the duplicate records from a table.

Below are the example queries to demonstrate the point no 4 and 5 from the Note points.

Example query for point number 4

Query to access a row quickly and easily using its row ID.

SELECT * 
FROM EMP1 
WHERE ROWID='AADZmhABAAAAck0AAA';

Example query for point number 5

Query to delete the records from the table using rowid;

DELETE 
FROM EMP1 
WHERE ROWID='AADZmhABAAAAck0AAA';

Query to delete the duplicate records using rowid.

for duplicate row delete please click on delete duplicate rows link.

When a rowid changes?

A rowid is assigned to a row upon insert and is immutable(never changing), changes happen only inthe following situations.

  1. when you update a partition key and row moves the partition.
  2. Flashback a table,since the flash back table command really issues a DELETE + INSERT to put the data back the way it was.
  3. When you move the table with alter command would change the rowids.

For point number 3 below is the example for better understanding purpose.



SELECT ROWID, ROWNUM,empno
  FROM EMP1 WHERE ROWNUM <=4;
ROWIDROWNUMEMPNO
AADZmhABAAAAckoAAA 11
AADZmhABAAAAckoAAB 22
AADZmhABAAAAckoAAC 33
AADZmhABAAAAckoAAD44
CREATE TABLE MYEMP AS
 SELECT * FROM EMP1 WHERE ROWNUM <=4;

 SELECT ROWID, ROWNUM,empno
 FROM MYEMP;
ROWIDROWNUMEMPNO
AADazGAAdBAAAInTAAA 11
AADazGAAdBAAAInTAAB 22
AADazGAAdBAAAInTAAC 33
AADazGAAdBAAAInTAAD 44
ALTER TABLE MYEMP MOVE;
SELECT ROWID, ROWNUM,empno
FROM MYEMP;
ROWIDROWNUMEMPNO
AADazKAAdAAAInrAAA11
AADazKAAdAAAInrAAB22
AADazKAAdAAAInrAAC33
AADazKAAdAAAInrAAD44

If we observe the above MYEMP table result, the rowid has changed after moving the table.

ROWNUM:

  • Oracle engine maintains the number of each record inserted by users in table.
  • Rownum generates sequence number for each record each record inserted by users in table.
  • By the help of ROWNUM clause we can access the data according to the record inserted.
  • Rownum is used to limit the number of records to fetch from the table.

if we run the below query, only first 4 records will be fetched from the table.

SELECT ROWID, ROWNUM,empno
FROM EMP1 WHERE ROWNUM <=4;
  • Order of rown num in the select query will change depeds on the order by clause.

for example,


SELECT ROWNUM,E.* 
FROM EMP1 E
ORDER BY MGR DESC;
ROWNUMEMPNO
ENAME
MGRHIREDATESALDEPTNAMESAMPLE
77CLARK6/9/19812450.00SERVICES
44JONES4/2/19812975.00INSURANCE
33WARD2/22/19811250.00BANKING
1212JAMES712/3/1981950.00SERVICES
66BLAKE75/1/19812850.00SERVICES
22ALLEN42/20/19811600.00INSURANCE
1414MILLER41/23/19821300.00INSURANCE
88SCOTT412/9/19823000.00INSURANCE
1313FORD412/3/19813000.00INSURANCE
1010TURNER
49/8/19811500.00INSURANCE
55MARTIN49/28/19811250.00
INSURANCE
1111ADAMS31/12/19831100.00BANKING
11SMITH312/17/1980800.00BANKING
99KING311/17/19815000.00BANKING

Notes:

  1. row num is a sequential number, allocated to each return row during query executuion.
  2. row num is a numeric number and is temporary(row num changes depends on the where condition in select query).
  3. using row num we can count the number of records.
  4. The row numbers of the records might change if an order by clause is used in the query(refer above example query).

ROW_NUMBER()

  1. ROW_NUMBER is an analytical function that assigns a number to each row according to its ordering within a group of rows.
  2. Row_Number generates sequence of numbers after performing sorting operation on specific column(order by clause on a column)
  3. Row_Number cannot be used in where clause.It can used to filter only after sorting by using outer query.
  4. ROW_NUMBER can contain duplicates when we use partition by on column.

for example queries please click on Oracle analytical functions.

Difference between ROWID, ROWNUM, ROW_NUMBER

SRNO
ROW_ID

ROW_NUM
ROW_NUMBER
1ROWID  is a pseudo columnROWNUM is a pseudo columnROW_NUMBER is a analytical function.
2Represents the physical address of rows.ROWNUM is the sequential number, allocated to each returned row during query execution.ROW_NUMBER assigns a number to each row according to its ordering within a group of rows.
3Rowid is permanentROWNUM is temporaryROW_NUMBER is temporary
4ROWID is 18 character stringROWNUM is numericROW_NUMBER is a function that returns numeric value.
5ROWID gives the address of rows or records.ROWNUM gives the count of recordsROW_NUMBER gives the rank of records.
6ROWID is automatically generated unique id at the time of insertion of row in the table.ROWNUM is a dynamic 
ROW_NUMBER is dynamic
7ROWID is the fastest means of accessing dataROWNUM is retrieved along with the select statement.ROW_NUMBER is retrieved along with the select statement.
8ROWIDs are unique identifiers for the any row in the table.ROWNUM is the sequential number in which oracle has retrieved the row(ROWNUM generated before sorting)ROW_NUMBER is the sequential number in which oracle has retrieved the row. ROW_NUMBER may contains duplicate if it performs portion by operation.(ROW_NUMBER generated after sorting operation)
9ROWID can be used in where clause for selecting, updating and deleting the rows.ROWNUM can be used in where clause for selecting, updating and deleting the rows.ROW_NUMBER can be used in where clause for selecting, updating and deleting the rows.
10Can delete duplicate rowsCan delete duplicate rowsCan delete duplicate rows

Below are the example query for point number 8 in difference between rowid , rownum, row_number()

Query example 1:

SELECT EMPNO,
       ROWID,
       ROWNUM,
       ROW_NUMBER() OVER(ORDER BY MGR) 
FROM EMP1 
ORDER BY MGR;

Output:

EMPNO
ROWID

ROWNUM
ROW_NUMBER()OVER(ORDERBYMGR)
19AADZmhABAAAAck0AAI91
21AADZmhABAAAAck0AAA12
311AADZmhABAAAAck0AAK113
414AADZmhABAAAAck0AAN144
58AADZmhABAAAAck0AAH85
62AADZmhABAAAAck0AAB26
715AADZmhABAAAAck0AAM137
810AADZmhABAAAAck0AAJ108
95AADZmhABAAAAck0AAE59
1012AADZmhABAAAAck0AAL1210
116AADZmhABAAAAck0AAF611
124AADZmhABAAAAck0AAD412
133AADZmhABAAAAck0AAC313
147AADZmhABAAAAck0AAG714

The above output clearly shows that rownum generated the sequence number before sorting(order by clause) and Row_number() function generated the sequence number after sorting(order by clause)

Query example 2:

Below query show the duplicate number generation for row_number() using partition by clause.

SELECT EMPNO,
       ROWID,
       ROWNUM,
       ROW_NUMBER() OVER(PARTITION BY MGR ORDER BY MGR) ROWNUMBER
FROM EMP1 
 ORDER BY MGR;

Output

EMPNOROWIDROWNUMROWNUMBER
19AADZmhABAAAAck0AAI91
21AADZmhABAAAAck0AAA12
311AADZmhABAAAAck0AAK113
414AADZmhABAAAAck0AAN141
58AADZmhABAAAAck0AAH82
62AADZmhABAAAAck0AAB23
715AADZmhABAAAAck0AAM134
810AADZmhABAAAAck0AAJ105
95AADZmhABAAAAck0AAE56
1012AADZmhABAAAAck0AAL121
116AADZmhABAAAAck0AAF62
124AADZmhABAAAAck0AAD41
133AADZmhABAAAAck0AAC32
147AADZmhABAAAAck0AAG73

Published inOracle PL SQL