Datatypes - Expert Oracle Database Architecture - page 575

ROWID/UROWID Types
The last datatypes to discuss are the ROWID and UROWID types. A ROWID is the address of a row in a table (remember
from Chapter 10 that it takes a ROWID plus a tablename to uniquely identify a row in a database). Sufficient information
is encoded in the ROWID to locate the row on disk, as well as identify the object the ROWID points to (the table and so
on). ROWID 's close relative, UROWID , is a universal ROWID and is used for tables, such as IOTs and tables accessed via
gateways to heterogeneous databases that do not have fixed ROWID s. The UROWID is a representation of the primary key
value of the row and hence will vary in size depending on the object it points to.
Every row in every table has either a ROWID or a UROWID associated with it. They are considered pseudo columns
when retrieved from a table, meaning they are not actually stored with the row, but rather are a derived attribute of
the row. A ROWID is generated based on the physical location of the row, it is not stored with it. A UROWID is generated
based on the row's primary key, so in a sense it is stored with the row, but not really, as the UROWID does not exist as a
discrete column, but rather as a function of the existing columns.
It used to be that for rows with ROWID s (the most common type of rows in Oracle; with the exception of rows
in IOTs, all rows have ROWID s), the ROWID s were immutable. When a row was inserted, it would be associated with
a ROWID , an address, and that ROWID would be associated with that row until it was deleted, until it was physically
removed from the database. Over time, this is becoming less true, as there are now operations that may cause a row's
ROWID to change, for example:

• Updating the partition key of a row in a partitioned table such that the row must move from

one partition to another

FLASHBACK table command to restore a database table to a prior point in time

• Using the

MOVE operations and many partition operations such as splitting or merge partitions •

ALTER TABLE SHRINK SPACE command to perform a segment shrink
Now, since ROWID s can change over time (since they are no longer immutable), it is not recommended to
physically store them as columns in database tables. That is, using a ROWID as a datatype of a database column is
considered a bad practice and should be avoided. The primary key of the row (which should be immutable) should
be used instead, and referential integrity can be in place to ensure data integrity is preserved. You cannot do this with
the ROWID types—you cannot create a foreign key from a child table to a parent table by ROWID , and you cannot enforce
integrity across tables like that. You must use the primary key constraint.
Of what use is the ROWID type, then? It is still useful in applications that allow the end user to interact with the
data—the ROWID , being a physical address of a row, is the fastest way to access a single row in any table. An application
that reads data out of the database and presents it to the end user can use the ROWID upon attempting to update that
row. The application must use the ROWID in combination with other fields or checksums (refer to Chapter 7 for further
information on application locking). In this fashion, you can update the row in question with the least amount of
work (e.g., no index lookup to find the row again) and ensure the row is the same row you read out in the first place by
verifying the column values have not changed. So, a ROWID is useful in applications that employ optimistic locking.

• Using the

Summary
In this chapter, we've examined many basic datatypes provided by Oracle; we've seen how they are physically stored
and what options are available with each. We started with character strings, the most basic of types, and looked
into considerations surrounding multibyte characters and raw binary data. We then discussed extended datatypes
(available in Oracle 12 c and above) and how this feature allows you to define VARCHAR2 , NVARCHAR2 , and RAW datatypes
to be as large as 32, 727 bytes. Next, we studied the numeric types, including the very precise Oracle NUMBER type and
the new floating-point types provided with Oracle 10 g and later.