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:

ROWID	EMPNO
AADZmhABAAAAckoAAA	1
AADZmhABAAAAckoAAB	2
AADZmhABAAAAckoAAC	3
AADZmhABAAAAckoAAD	4
AADZmhABAAAAckoAAE	5
AADZmhABAAAAckoAAF	6
AADZmhABAAAAckoAAG	7
AADZmhABAAAAckoAAH	8
AADZmhABAAAAckoAAI	9
AADZmhABAAAAckoAAJ	10
AADZmhABAAAAckoAAK	11
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;

ROWID	ROWNUM	EMPNO
AADZmhABAAAAckoAAA	1	1
AADZmhABAAAAckoAAB	2	2
AADZmhABAAAAckoAAC	3	3
AADZmhABAAAAckoAAD	4	4

CREATE TABLE MYEMP AS
 SELECT * FROM EMP1 WHERE ROWNUM <=4;

 SELECT ROWID, ROWNUM,empno
 FROM MYEMP;

ROWID	ROWNUM	EMPNO
AADazGAAdBAAAInTAAA	1	1
AADazGAAdBAAAInTAAB	2	2
AADazGAAdBAAAInTAAC	3	3
AADazGAAdBAAAInTAAD	4	4

ALTER TABLE MYEMP MOVE;
SELECT ROWID, ROWNUM,empno
FROM MYEMP;

ROWID	ROWNUM	EMPNO
AADazKAAdAAAInrAAA	1	1
AADazKAAdAAAInrAAB	2	2
AADazKAAdAAAInrAAC	3	3
AADazKAAdAAAInrAAD	4	4

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;

ROWNUM	EMPNO	ENAME	MGR	HIREDATE	SAL	DEPTNAME	SAMPLE
7	7	CLARK		6/9/1981	2450.00	SERVICES
4	4	JONES		4/2/1981	2975.00	INSURANCE
3	3	WARD		2/22/1981	1250.00	BANKING
12	12	JAMES	7	12/3/1981	950.00	SERVICES
6	6	BLAKE	7	5/1/1981	2850.00	SERVICES
2	2	ALLEN	4	2/20/1981	1600.00	INSURANCE
14	14	MILLER	4	1/23/1982	1300.00	INSURANCE
8	8	SCOTT	4	12/9/1982	3000.00	INSURANCE
13	13	FORD	4	12/3/1981	3000.00	INSURANCE
10	10	TURNER	4	9/8/1981	1500.00	INSURANCE
5	5	MARTIN	4	9/28/1981	1250.00	INSURANCE
11	11	ADAMS	3	1/12/1983	1100.00	BANKING
1	1	SMITH	3	12/17/1980	800.00	BANKING
9	9	KING	3	11/17/1981	5000.00	BANKING

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
1	ROWID  is a pseudo column	ROWNUM is a pseudo column	ROW_NUMBER is a analytical function.
2	Represents 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.
3	Rowid is permanent	ROWNUM is temporary	ROW_NUMBER is temporary
4	ROWID is 18 character string	ROWNUM is numeric	ROW_NUMBER is a function that returns numeric value.
5	ROWID gives the address of rows or records.	ROWNUM gives the count of records	ROW_NUMBER gives the rank of records.
6	ROWID is automatically generated unique id at the time of insertion of row in the table.	ROWNUM is a dynamic	ROW_NUMBER is dynamic
7	ROWID is the fastest means of accessing data	ROWNUM is retrieved along with the select statement.	ROW_NUMBER is retrieved along with the select statement.
8	ROWIDs 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)
9	ROWID 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.
10	Can delete duplicate rows	Can delete duplicate rows	Can 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)
1	9	AADZmhABAAAAck0AAI	9	1
2	1	AADZmhABAAAAck0AAA	1	2
3	11	AADZmhABAAAAck0AAK	11	3
4	14	AADZmhABAAAAck0AAN	14	4
5	8	AADZmhABAAAAck0AAH	8	5
6	2	AADZmhABAAAAck0AAB	2	6
7	15	AADZmhABAAAAck0AAM	13	7
8	10	AADZmhABAAAAck0AAJ	10	8
9	5	AADZmhABAAAAck0AAE	5	9
10	12	AADZmhABAAAAck0AAL	12	10
11	6	AADZmhABAAAAck0AAF	6	11
12	4	AADZmhABAAAAck0AAD	4	12
13	3	AADZmhABAAAAck0AAC	3	13
14	7	AADZmhABAAAAck0AAG	7	14

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

	EMPNO	ROWID	ROWNUM	ROWNUMBER
1	9	AADZmhABAAAAck0AAI	9	1
2	1	AADZmhABAAAAck0AAA	1	2
3	11	AADZmhABAAAAck0AAK	11	3
4	14	AADZmhABAAAAck0AAN	14	1
5	8	AADZmhABAAAAck0AAH	8	2
6	2	AADZmhABAAAAck0AAB	2	3
7	15	AADZmhABAAAAck0AAM	13	4
8	10	AADZmhABAAAAck0AAJ	10	5
9	5	AADZmhABAAAAck0AAE	5	6
10	12	AADZmhABAAAAck0AAL	12	1
11	6	AADZmhABAAAAck0AAF	6	2
12	4	AADZmhABAAAAck0AAD	4	1
13	3	AADZmhABAAAAck0AAC	3	2
14	7	AADZmhABAAAAck0AAG	7	3