ROWID Format

ROWID Format

Extended ROWID

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:

SQL> SELECT department_id, rowid FROM hr.departments;

DEPARTMENT_ID ROWID

————- ——————

10 AAABQMAAFAAAAA6AAA

20 AAABQMAAFAAAAA6AAB

30 AAABQMAAFAAAAA6AAC

40 AAABQMAAFAAAAA6AAD

50 AAABQMAAFAAAAA6AAE

60 AAABQMAAFAAAAA6AAF

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

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.

Restricted Rowids

SELECT ROWID, last_name FROM employees 
    WHERE department_id = 30; 

can return the following row information:

ROWID              ENAME 
------------------ ---------- 
00000DD5.0000.0001 KRISHNAN 
00000DD5.0001.0001 ARBUCKLE 
00000DD5.0002.0001 NGUYEN 

As shown, a restricted rowid’s VARCHAR2/hexadecimal representation is in a three-piece format, block.row.file:

  • The data block that contains the row (block DD5 in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.
  • The row in the block that contains the row (rows 0, 1, 2 in the example). Row numbers of a given block always start with 0.
  • The datafile that contains the row (file 1 in the example). The first datafile of every database is always 1, and file numbers are unique within a database.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: