ROWID

ROWID

For each row in the database, the ROWID pseudocolumn returns the physical address of the row.

Oracle Database rowid values contain information necessary to locate a row:

-The data object number of the object

-The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

-The data block in the datafile in which the row resides

-The position of the row in the data block (first row is 0)

The ROWID variable can improve PL/SQL programs that retrieve records from the database, perform manipulation on the column values, and then complete with an UPDATE to the retrieved record. When retrieving each record, the ROWID can be added to the selected column list. When updating each record, the ROWID can be used in the predicate clause. The ROWID is the fastest access path to a record in a table, even faster than a unique index reference.

Usually, a rowid value uniquely identifies a row in the database. rows in different tables that are stored together in the same cluster can have the same rowid.

You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

a partitioned table – if you update the partition key and the update causes the row to move from one partition to another – the rowid will CHANGE.

ROWIDTOCHAR allows to display a rowid:

SQL> select ROWIDTOCHAR(rowid) from dual;

ROWIDTOCHAR(ROWID)

——————

AAAAECAABAAAAgiAAA

Object id: AAAAEC (=258), File id: AAB (=1), Block number: AAAAgi (=2082), Row number: AAA (=0)

DBMS_ROWID package allows to split rowid information:

SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) “OBJECT”,

2         DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) “FILE”,

3         DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) “BLOCK”,

4         DBMS_ROWID.ROWID_ROW_NUMBER(rowid) “ROW”

5  from dual.

6  /

OBJECT       FILE      BLOCK        ROW

———- ———- ———- ———-

258          1       2082          0

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