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 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.
SELECT rowid,empno FROM EMP1;
- ROWID is representative of the allocation of physical memory.
- Oracle automatically generates a unique ROWID at the time of insertion of a row.
- 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.
- A user can access a row quickly and easily using its row ID.
- 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.
- when you update a partition key and row moves the partition.
- Flashback a table,since the flash back table command really issues a DELETE + INSERT to put the data back the way it was.
- 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;
CREATE TABLE MYEMP AS SELECT * FROM EMP1 WHERE ROWNUM <=4; SELECT ROWID, ROWNUM,empno FROM MYEMP;
ALTER TABLE MYEMP MOVE; SELECT ROWID, ROWNUM,empno FROM MYEMP;
If we observe the above MYEMP table result, the rowid has changed after moving the table.
- 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.
SELECT ROWNUM,E.* FROM EMP1 E ORDER BY MGR DESC;
- row num is a sequential number, allocated to each return row during query executuion.
- row num is a numeric number and is temporary(row num changes depends on the where condition in select query).
- using row num we can count the number of records.
- The row numbers of the records might change if an order by clause is used in the query(refer above example query).
- ROW_NUMBER is an analytical function that assigns a number to each row according to its ordering within a group of rows.
- Row_Number generates sequence of numbers after performing sorting operation on specific column(order by clause on a column)
- Row_Number cannot be used in where clause.It can used to filter only after sorting by using outer query.
- 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
|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;
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;