ROWID (oracle) - any use for it ?

Question

My understanding is that ROWID is a unique value for each row in the result returned by a query. why we need this ROWID ?( there is already ROWNUM is ORACLE). Have any one used ROWID in sql query?

Answer 1

ROWID uniquely identifies a row within a table. ROWNUM gives you the row number of a result for a specific query. The two are very different and are not interchangeable.

Also there is ROW_NUMBER which is a more modern version of ROWNUM, and behaves slightly differently. Check out this article which explains the difference.

Answer 2

ROWID basically allows you to have two rows with the exact same data. While, you typically want your Primary Key to be a little more meaningful than a RowID, it is just a simple way of automatically ensuring uniqueness between rows.

Answer 3

ROWID is the physical location of a row. Consequently it is the fastest way of locating a row, faster even than a primary key lookup. So it can be useful in certain types of transaction where we select some rows, store their ROWIDs and then later on use the ROWIDs in where clauses for DML against those same rows.

The Oracle SELECT ... FOR UPDATE syntax implicitly uses ROWID, when we update the locked row using WHERE CURRENT OF. Also the EXCEPTIONS table (referenced when applying constraints with the EXCEPTIONS INTO clause) has a column ROW_ID. This allows us to quickly identify the rows which are breaking our constraint.

That latter example points to another general usage: when we are writing some generic piece of code and need a mechanism for storing UIDs without concerns regarding data type, composite keys, etc.

ROWNUM on the other hand is a pseudo-column which tags a row in a given result set. It has no permanent significance.

edit

The ROWID for a given record can change over the lifetime of a system, for instance through a table rebuild. Also if one record is deleted a new record could be given that ROWID. Consequently ROWIDs are not suitable for use as UIDs in the long term. But they are good enough for use within a transaction.

Answer 4

I now know an example for this.

suppose you have table with no primary keys. so this table can have duplicate rows. How would you delete duplicate rows but keep exactly one of that kind?

Oracle provides ROWID as a kind of substitute for primary key. You can write a nested query which is of correlated type [(group by all columns in the row and take MIN(ROWID) in each group in inner query, for each group delete the other rows in the group in outerquery)]

Example

SQL> select * from employees;

       SSN NAME
---------- ----------
         1 helen
         1 helen
         2 helen
         2 peter
        10 sally
        11 null
        11 null
        12 null

8 rows selected.

SQL> delete from employees where ROWID NOT IN (select min(ROWID) from employees
group by ssn,name);

2 rows deleted.

SQL> select * from employees;

       SSN NAME
---------- ----------
         1 helen
         2 helen
         2 peter
        10 sally
        11 null
        12 null

6 rows selected.

Answer 5

note that ROWID does not persist across a database EXPORT and IMPORT cycle. you should NEVER store a rowid in your tables as a key value.