Oracle PL/SQL Programming, 5th Edition

The UROWID and ROWID Datatypes

The UROWID and ROWID types allow you to work with database ROWIDs in your PL/SQL programs. A ROWID is a row identifier—a binary value that identifies the physical address for a row of data in a database table. A ROWID can be used to uniquely identify a row in table, even if that table does not have a unique key. Two rows with identical column values will have different ROWIDs or UROWIDs.

Warning

Beware! ROWIDs in a table can change. In early Oracle releases (Oracle8 Database 8.0 and earlier) ROWIDs could not change during the life of a row. But starting with Oracle8i Database new features were added that violate this old rule. If row movement is enabled on a regular (heap organized) table or for any index-organized table, updates can cause a row’s ROWID or UROWID to change. In addition, if someone alters the table to shrink, move, or perform some other operation that will cause a row to change from one physical data block to another, the ROWID will change.

With the caveat noted above, there can still sometimes be value in using ROWIDs. Referencing ROWIDs in SELECT, UPDATE, MERGE, and DELETE statements can lead to desirable improvements in processing speed, as access by ROWID is the fastest way to locate or retrieve a specific row in a table—faster than a search by primary key. Figure 13-1 contrasts the use of a ROWID in an UPDATE statement with the use of column values such as those for a primary key.

Figure 13-1. ROWIDs take you directly to rows in a ...