****************
ROWID Data Type
****************

ROWID is a pseudo-column that can be queried along with other columns in a table. It
has the following characteristics:
• ROWID is a unique identifier for each row in the database.
• ROWID is not stored explicitly as a column value.
• Although the ROWID does not directly give the physical address of a row, it can
be used to locate the row.
• ROWID provides the fastest means of accessing a row in a table.
• ROWIDs are stored in indexes to specify rows with a given set of key values.
With release 8.1, the Oracle server provides a new single data type called the universal
rowid, or UROWID. It supports rowids of foreign tables (tables other than Oracle’s)
and can store all kinds of rowids. The value of the parameter COMPATIBLE must be
set to 8.1 or higher to use UROWID.


****************
ROWID Format
****************
ROWID needs 10 bytes of storage on disk and is displayed using 18 characters. It
consists of the following components:
• Data object number is assigned to each data object, such as a table or index, when
it is created, and it is unique within the database.
• Relative file number is unique to each file within a tablespace.
• Block number represents the position of the block containing the row within the
file.
• Row number identifies the position of the row directory slot in the block header.
Internally, the data object number needs 32 bits, the relative file number needs 10 bits,
the block number needs 22 bits, and the row number needs 16 bits, adding up to a total
of 80 bits or 10 bytes.
ROWID is displayed using a base-64 encoding scheme, which uses six positions for
the data object number, three positions for the relative file number, six positions for the
block number, and three positions for the row number. The base-64 encoding scheme
uses the characters “A-Z,” “a-z,” “0-9,” “+,” and “/”—a total of 64 characters, as in the
example below:
SQL> SELECT id, ROWID FROM summit.department;
ID ROWID
--------- ------------------
10 AAADC4AACAAAAMAAAA
31 AAADC4AACAAAAMAAAB
32 AAADC4AACAAAAMAAAC
33 AAADC4AACAAAAMAAAD
34 AAADC4AACAAAAMAAAE
35 AAADC4AACAAAAMAAAF
41 AAADC4AACAAAAMAAAG
42 AAADC4AACAAAAMAAAH
43 AAADC4AACAAAAMAAAI
44 AAADC4AACAAAAMAAAJ
45 AAADC4AACAAAAMAAAK
50 AAADC4AACAAAAMAAAL
In this example:
• AAADC4 is the data object number.
• AAC is the relative file number.
• AAAAMA is the block number.
• AAA is the row number for the department with ID=10.

****************
Locating a Row Using ROWID
****************

Because a segment can only reside in one tablespace, using the data object number, the
Oracle server can determine the tablespace that contains a row.
The relative file number within the tablespace is used to locate the file, the block
number is used to locate the block containing the row, and the row number is used to
locate the row directory entry for the row.
The row directory entry can be used to locate the beginning of the row.
Thus, ROWID can be used to locate any row within a database.

****************
Using Restricted ROWID in Oracle7 and Earlier
****************

Versions of Oracle prior to the Oracle8 server used the restricted ROWID format. A
restricted ROWID used only six bytes internally and did not contain the data object
number. This format was acceptable in Oracle7 or an earlier release because the file
numbers were unique within a database. Thus, earlier releases did not permit more
than 1,022 data files.
Even though Oracle8 removed this restriction by using tablespace-relative file
numbers, the restricted ROWID is still used in objects like nonpartitioned indexes on
nonpartitioned tables where all the index entries refer to rows within the same
segment.

Restricted ROWID
• Can identify rows within a segment
• Needs less space
BBBBBBBB FFFF RRRR
Block number Row number File number
. .