July 19, 2007 Leave a comment
An extended ROWID needs 10 bytes of storage on disk and is displayed by using 18 characters. It consists of the following components:
• Data object number: Is assigned to each data object, such as 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, block number needs 22 bits, and the row number needs 16 bits, adding up to a total of 80 bits or 10 bytes.
An extended 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 characters A-Z, a-z, 0-9, and /. This is a total of 64 characters, as in the following example:
ROWID Format (continued)
SQL> SELECT department_id, rowid FROM hr.departments;
In this example:
• AAABQM is the data object number
• AAF is the relative file number
• AAAAA6 is the block number
• AAA is the row number for the department with ID = 10
Restricted ROWID in Oracle7 and earlier:
Versions of the Oracle database prior to Oracle8 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 datafiles. Now it is the limit for a tablespace.
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.
Locating a row using ROWID:
Because a segment can only reside in one tablespace, by 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.
Structure of a Row
Row data is stored in database blocks as variable-length records. Columns for a row are generally stored in the order in which they are defined and any trailing NULL columns are not stored.
Note: A single byte for column length is required for non trailing NULL columns. Each row in a table has:
• Row header: Used to store the number of columns in the row, the chaining information, and the row lock status
• Row data: For each column, the Oracle server stores the column length and value (One byte is needed to store the column length if the column will require more than 250 bytes of storage in which case three bytes will be used for column length. The column value is stored immediately following the column length bytes.)
Adjacent rows do not need any space between them. Each row in the block has a slot in the row directory. The directory slot points to the beginning of the row.
Sent by Hasan Tonguc Yılmaz