O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required