Oracle ROWID as function/procedure parameter

Question

I just would like to hear different opinions about ROWID type usage as input parameter of any function or procedure.

I have normally used and seen primary keys used as input parameters but is there some kind of disadvantages to use ROWID as input parameter? I think it's kind a simple and selects are pretty quick if used in WHERE clause.

For example:

FUNCTION get_row(p_rowid IN ROWID) RETURN TABLE%ROWTYPE IS...

Answer 1

From the concept guide:

Physical rowids provide the fastest possible access to a row of a given table. They contain the physical address of a row (down to the specific block) and allow you to retrieve the row in a single block access. Oracle guarantees that as long as the row exists, its rowid does not change.

The main drawback of a ROWID is that while it is normally stable, it can change under some circumstances:

• The table is rebuilt (ALTER TABLE MOVE...)
• Export / Import obviously
• Partition table with row movement enable

A primary key identifies a row logically, you will always find the correct row, even after a delete+insert. A ROWID identifies the row physically and is not as persistent as a primary key.

You can safely use ROWID in a single SQL statement since Oracle will guarantee the result is coherent, for example to remove duplicates in a table. To be on the safe side, I would suggest you only use the ROWID accross statements when you have a lock on the row (SELECT ... FOR UPDATE).

From a performance point of view, the Primary key access is a bit more expensive but you will normally notice this only if you do a lot of single row access. If performance is critical though, you usually can get greater benefit in that case from using set processing than single row processing with rowid. In particular, if there are a lot of roundtrips between the DB and the application, the cost of the row access will probably be negligible compared to the roundtrips cost.