Difference between ROWNUM and ROWID in Oracle

First let us examine what is ROWID and ROWNUM

Both ROWID and ROWNUM are Oracle’s pseudo-columns

ROWID

The ROWID pseudo-column returns the address of the row.

ROWID contains information about:

  • The data object number of the object
  • The data block in the data file in which the row resides
  • The position of the row in the data block (first row is 0)
  • The data file in which the row resides (first file is 1). The file number is relative to the Tablespace.
SQL> select employee_id,first_name,rowid from employees where rownum < 5;
EMPLOYEE_ID FIRST_NAME           ROWID             
----------- -------------------- --
100 Steven  AAATiDAAMAAALKzAAA
101 Neena   AAATiDAAMAAALKzAAB
102 Lex     AAATiDAAMAAALKzAAC
103 Alexander AAATiDAAMAAALKzAAD

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 pseudo-column have the data type ROWID or UROWID.

Usage

  • 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.
  • ROWID can be used to identify and delete duplicate records ( See post 5 ways to delete duplicate records )

Things to Remember

1) You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

2) Although you can use the ROWID pseudo-column in the SELECT and WHERE clause of a query, these pseudo-column values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudo-column.

SQL> create table test1(no number,rid rowid);
Table created.

SQL> insert into test1(no) values(1);
1 row created.

SQL> insert into test1(no) values(2);
1 row created.

SQL> insert into test1(no) values(3);
1 row created.

SQL> select no,rowid from test1;

        NO ROWID             
---------- ------------------
         1 AAATk9AAMAAAAHXAAA
         2 AAATk9AAMAAAAHXAAB
         3 AAATk9AAMAAAAHXAAC

SQL> update test1 set rid = (select rowid from test1 where no=1) where no = 1;

1 row updated.

SQL> update test1 set rid = (select rowid from test1 where no=2) where no = 2;
1 row updated.

SQL> update test1 set rid = (select rowid from test1 where no=3) where no = 3;
1 row updated.

SQL> select no,rid,rowid from test1;

        NO RID                ROWID             
---------- ------------------ ------------------
1 AAATk9AAMAAAAHXAAA AAATk9AAMAAAAHXAAA
2 AAATk9AAMAAAAHXAAB AAATk9AAMAAAAHXAAB
3 AAATk9AAMAAAAHXAAC AAATk9AAMAAAAHXAAC

ROWNUM

ROWNUM is a pseudo-column to return the row number of the row in a table. The order will be based on how Oracle selects a row from table.

Example

Note that these examples are tested in Oracle 19c installed on Oracle Linux Server 7.6 (VM)

SQL> select banner from v$version;

BANNER                                                                          
--------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production  

SQL> host
[oracle@localhost oracle]$ cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.6"

SQL> select employee_id,first_name,rownum from employees where rownum < 5;
EMPLOYEE_ID FIRST_NAME               ROWNUM
----------- -------------------- ----------
        100 Steven                        1
        101 Neena                         2
        102 Lex                           3
        103 Alexander                     4


SQL> select * from (select employee_id,first_name,rownum from employees order by employee_id desc )where rownum < 5;
EMPLOYEE_ID FIRST_NAME               ROWNUM
----------- -------------------- ----------
        206 William                       1
        205 Shelley                       2
        204 Hermann                       3
        203 Susan                         4

Few more tips about ROWNUM

  1. Do not write ROWNUM = 0 ( There is no record having ROWNUM = 0)
  2. Do not write ROWNUM <= 1 instead write ROWNUM = 1
  3. In general Oracle assigns ROWNUM while retrieving the records, it is not based on Primary key column
  4. Do not write ROWNUM > 1, which will not yield any results
  5. ROWNUM and ROW_NUMBER() is not same

Difference

ROWIDROWNUM
pseudo-columnpseudo-column
It is kind of a unique row locatorRow number of the selected rows from a select query
ROWID remains constantROWNUM changes based the select query
ROWID is alphanumeric valueROWNUM is always a number

Conclusion

We have learned about ROWID, ROWNUM and their differences. ROWID and ROWNUM are entirely two different features. ROWID is useful in locating a row uniquely while ROWNUM is for assigning a number of the selected records.

———————-

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s