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.
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.
In the above code you can see the first column ROWID.
You can use ROWID in the WHERE condition as well.
I am assuming you have understood the concept to ROWID.
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.
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.
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.
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.
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.
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.