Thinking in ROWID

SA,
We hear ‘ROWID’ many times from database developers, So we should think about what purpose it serves.

Oracle says, ‘They are the fastest way to access a single row’

ROWID gives us the location of the data for a given row in a table. The datafile – then the position of the row in the block, then the data block in the datafile. 

As you can imagine, this data is UNIQUE. So, each row in your table will have a unique ROWID. 

This is only because the data for 2 rows can’t exist in the same space.

It’s not uncommon for a table to lack any unique columns or PKs or unique indexes. But, if you know the ROWID, then you will have at least 1 unique differentiator for that record.

It’s also important to remember that a record’s ROWID is not guaranteed to remain unchanged.

Since it’s a PSEUDO column, it’s not actually stored in the database. That means we can’t INSERT, UPDATE, DELETE  ROWID. But, we can use it in a SELECT or WHERE.

Oracle is saying ROWIDs aren’t stored in a table. They can and WILL be stored in an INDEX. Indexes allow us to quickly jump to the records we are looking for. However, the ROWID is dynamically assembled as you retrieve that data.  

Main majors use of ROWIDs

1. Use it to delete duplicate rows from your table.
2. Use it to edit SELECT results in Toad for Oracle

Deleting Records
Add ‘ROWID’ to your SELECT string. Once you’ve identified the records you want to delete, just record those values. Then issue your ‘DELETE FROM TABLE WHERE ROWID in (…).

select emp.ROWID, empno , ename

from scott.emp;

If you query a view and include ROWID, you may get a

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Your view could be pulling data from multiple source tables. These tables need a PK apparently. This Oracle’s way of saying that it can’t determine the the ROWID based on what you are querying. Also, your row could be pulling from multiple sources, so there would be theoretically multiple ROWIDs

I hope that post is helpful and useful 

Mahmoud Ahmed El-Sayed