Hello guys,
Today we will talk about 2 very important Pseudocolumns – ROWID and ROWNUM.
A pseudocolumn behaves like a table column but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.
ROWID
In the Oracle database for each row, the ROWID pseudocolumn returns the address of the row.
It means whenever a new row is inserted into a table, Oracle generates a ROWID and stores it into the ROWID pseudocolumn.
Oracle Database rowid values contain information necessary to locate a row:
• The data object number of the object
• The data block in the datafile in which the row resides
• The position of the row in the data block (first row is 0)
• The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.
Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.
Values of the ROWID pseudocolumn have the datatype ROWID or UROWID.
Rowid values have several important uses:
• They are the fastest way to access a single row.
• They can show you how the rows in a table are stored.
• They are unique identifiers for rows in a table.
You can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query.
Example:
--Create a table T1 with a few columns
CREATE TABLE T1 ( SR_NO NUMBER (5),FNAME VARCHAR2 (20),LNAME VARCHAR2 (20));
--Insert a few rows into the table T1
INSERT INTO T1 VALUES (1,'WILL','SMITH');
INSERT INTO T1 VALUES (2,'JOHN','MILTON');
INSERT INTO T1 VALUES (3,'JORGE','BERNARD');
INSERT INTO T1 VALUES (4,'WILLIAM','SHAKESPEARE');
INSERT INTO T1 VALUES (5,'JOHN','DRYDEN');
--Select ROWID with the columns from T1 table
SELECT ROWID,SR_NO,FNAME,LNAME FROM T1;
--Output
ROWID SR_NO FNAME LNAME
------------------ ---------- -------------------- --------------------
AAADVJAAEAAAADVAAA 1 WILL SMITH
AAADVJAAEAAAADVAAB 2 JOHN MILTON
AAADVJAAEAAAADVAAC 3 JORGE BERNARD
AAADVJAAEAAAADVAAD 4 WILLIAM SHAKESPEARE
AAADVJAAEAAAADVAAE 5 JOHN DRYDEN
In the above code you can see the first column ROWID.
You can use ROWID in the WHERE condition as well.
Example:
SELECT ROWID,SR_NO,FNAME,LNAME FROM T1 WHERE ROWID = ‘AAADVJAAEAAAADVAAE’;
--Output
ROWID SR_NO FNAME LNAME
------------------ ---------- -------------------- --------------------
AAADVJAAEAAAADVAAE 5 JOHN DRYDEN
I am assuming you have understood the concept to ROWID.
ROWNUM
In the Oracle database for each row, the ROWNUM pseudocolumn returns the number of the row.
Same as ROWID whenever a new row is inserted into a table, Oracle generates a row number and stores it into the ROWNUM pseudocolumn but this ROWNUM column contains the values as 1, 2, 3 and so on.
Let’s do some examples and see how we can use ROWNUM pseudocolumn.
As we have already created a table T1, let’s query this table.
SELECT ROWNUM,SR_NO,FNAME,LNAME FROM T1;
--Output
ROWNUM SR_NO FNAME LNAME
---------- ---------- -------------------- --------------------
1 1 WILL SMITH
2 2 JOHN MILTON
3 3 JORGE BERNARD
4 4 WILLIAM SHAKESPEARE
5 5 JOHN DRYDEN
As you can see that Oracle has generated ROW NUMBER for each row. It is same as SR_NO column which is a part of Table – T1.
Oracle engine always check for the WHERE clause if exists and then assign a ROWNUM to a ROW.
If there is not a where clause in the query, Oracle engine will show all the records in the table and add a row number in the output as 1, 2, 3 and so on. As it is showing in the above result
But if you add a where clause to the query, it (Oracle engine) will figure out the result and then will add a row number to each row in the same order as 1, 2, 3 and so on.
Let’s add a where clause to the above select statement.
SELECT ROWNUM,SR_NO,FNAME,LNAME FROM T1
Where SR_NO in (2,4,5);
--Output
ROWNUM SR_NO FNAME LNAME
---------- ---------- -------------------- --------------------
1 2 JOHN MILTON
2 4 WILLIAM SHAKESPEARE
3 5 JOHN DRYDEN
You can see clearly in the output that ROWNUM is still showing as 1,2 and 3 but other field are not same as 1st result where we did not use a where clause.
It means you can never use ROWNUM column in the where clause if you want to fetch a result with an exact rownum but it will always show a result where rownum is 1.
SELECT ROWNUM,SR_NO,FNAME,LNAME FROM T1
Where rownum = 4;
Above query will not fetch any records but when you replace where clause with rownum = 1 and will show the first row of the table.
SELECT ROWNUM,SR_NO,FNAME,LNAME FROM T1
Where rownum = 1;
--Output
ROWNUM SR_NO FNAME LNAME
---------- ---------- -------------------- --------------------
1 1 WILL SMITH
Although, you can use ROWNUM in where clause with ‘<’ or ‘<=’ comparison operators but not with the ‘>’ or ‘>=’.
Hope the reason behind it, is clear to you.
You cannot use this ROWNUM column as a primary key but you can update any column which is defined as Primary Key with ROWNUM column values.
Update T1
SET SR_NO = ROWNUM;
Above query will update all SR_NO column with ROWNUM column. Do not specify where clause.
That’s all about ROWNUM and ROWID pseudocolumns. If you have any questions or suggestions please comment below.
You can also send me an email.
Regards,
Kapil Kumar
