Oracle rowID and Urowid

Oracle uses the rowid data type to store row addresses , rowID can be divided into two types, respectively, suitable for different

physical rowids: storage ordinary table,clustered table,table partition and Subpartition,indexe,index partition and Subpartition

Logical rowids: Store The line address of the IOT

Another type of rowid , called Universal rowed (UROWID), supports the physical rowid and logical rowed above , and supports non- Oracle table, which supports all types of rowid, but compatible must be at 8.1 or higher.

1.1 ROWID Pseudo-Column

Each table has a ROWID pseudo-column within Oracle, which cannot be displayed in all SQL and does not occupy storage space; It is used to query the address of a row from a table or to make a reference in where, an example is as follows:

SELECT ROWID, last_name from employees;

Oracle internally uses values that remain in the ROWID pseudo-column to build the index structure

     once again, the ROWID pseudo-column is not stored in the database, it is not the database data, this is the logical structure of databases and table, in fact, In the physical structure each row consists of one or more row pieces composition each piece address, i.e. rowid. In this sense, rowid still takes up disk space .

When we create a table, we can specify the column as the rowid data type, but Oracle does not guarantee that the data in the column is a valid rowid value , which must be guaranteed by the application , In addition , a column of type rowid requires 6 bytes to store data

1.2, physical Rowids

Only in the presence of the line, its physical address rowid will not change, unless Export/import, according to ROWID can directly locate the block to fetch data, so physical both have high stability (stability) and high performance (performance ) characteristics.

One thing to note here is that for clustered table, depending on its storage characteristics, the rows of different table in the same block may have the same rowid; Nonclustered table, each row or initial row slice (initial row piece) has a unique rowid

Note that the address of the ROWID is fixed, and after a row of a block is deleted and commits, it occupies an address that can be reused by the new insert row of the other transaction.

Physical ROWID can be any one of the following formats:

1) Extended rowID

Use table space-related block addresses, 8i and above using this format

2) Restricted rowID

Oracle version 7 or earlier uses a database-scoped address

1.2.1 extened rowID

The extended line address is a 64 encoded physical address encoded with a-Z, A-Z, 0-9 , +, and/.

Made up of 4 parts oooooofffbbbbbbrrr (obj#file#block#row#)

Oooooo-–data Object Number

fff–-data file number relative to table space

bbbbbb–-Block number

RRR---line number

Note Not a 16 binary representation

Sql> Select Rowid,name from obj$ where rownum<=10;

ROWID NAME

------------------ ------------------------------

AAAAASAABAAAAB6ABC access$

Aaaaasaabaaac1qaak Aggxmlimp

Aaaaasaabaaac1qaal Aggxqimp

Aaaaasaabaaagiraai ALERT_QT

Aaaaasaabaaagiraah Alert_que

Aaaaasaabaaagujaao alert_que$1

Aaaaasaabaaagujaap alert_que$1

AAAAASAABAAAGIRAAF Alert_que_n

Aaaaasaabaaagiraae Alert_que_r

Aaaaasaabaaagiraag Alert_type

We can use Dbms_rowid to extract pieces of information from extened rowID, or convert extened rowid to restricted rowed, see the SYS.DBMS_ROWID specification for detailed information

# according to rowID draw a pair of image number

Sql> Select Dbms_rowid.rowid_object (' Aaaaasaabaaagiraag ') obj# from dual;

obj#

----------

18

# according to rowID Extract table space relative file number

Sql> Select Dbms_rowid.rowid_relative_fno (' Aaaaasaabaaagiraag ') rfile# from dual;

rfile#

----------

1

# according to rowID Extract block number

Sql> Select Dbms_rowid. Rowid_block_number (' Aaaaasaabaaagiraag ') block# from dual;

block#

----------

26769

# according to rowID Extracting line Numbers

Sql> Select Dbms_rowid.rowid_row_number (' Aaaaasaabaaagiraag ') row# from dual;

row#

----------

6

# will be Extended rowID converted into restricted rowID

Sql> Select dbms_rowid.rowid_to_restricted (' Aaaaasaabaaagiraag ', 0) Restricted_rowid from dual;

Restricted_rowid

------------------

00006891.0006.0001

1.2.2 restricted rowID

The Limit Address line number is not the same as the extended Address line number, which is represented internally using binary notation, and when queried with Select, it is converted into a mixed form of VARCHAR2/16, which is organized as follows:

BBBBBBBB. RRRR. FFFF (block#.row#.file#)

Note that the file number here is the absolute file number, while extended rowid is relative to the file number (relative to the tablespace)

There is no longer an object number in Restricted rowID because a data block can be uniquely determined from absolute file numbers

Examples can refer to the previous 00006891.0006.0001

Also, note that the line number in the block starts at 0

In addition to using DBMS_ROWID to extract different parts of ROWID, you can also use substr

#extended rowID

Sql> SELECT ROWID,

Article from http://blog.itpub.net/94384/viewspace-600306/

Oracle rowID and Urowid