Introduction to Oracle's ROWID

ROWID is a rownum-like pseudo column that locates a relative unique address value for a record in a database. Typically, the value is inserted into the database table when the row data is

is determined and unique. For clustered tables, because of the clustering characteristics, the records on different tables are stored on the same cluster, so they will have the same rowid. Most operations of the database are through

rowID to complete, and the use of ROWID to make a single record positioning speed is the fastest. A description of the ROWID is given below.

The characteristic composition and use of ROWID

1, characteristics

Relative uniqueness (not unique on a clustered table)

Once determined, do not change at will

Use 10 byte storage (extended ROWID) to display a 18-bit string

In special cases, ROWID will change (as in the following cases)

Import Export operations for tables

ALTER TABLE Tab_name move

ALTER TABLE tab_name shrink space

Flashback Table Tab_name

Split partition Table

When a value is updated on a partitioned table, the record is moved to the new partition

Merge two partitions

2. Composition (extended ROWID)

Object number of the database object

File number of the file that contains the database object

Number of blocks on database objects

Line number on block (starting value is 0)

3. Use

Quickly locate Single-line Records

How the display row is stored on the table

Unique identifier of a row on the table

Used as a data type column_name ROWID

4, limit rowid, extend ROWID

Limit ROWID for early Oracle versions (prior to Oracle 8), ROWID consists of file#+block#+row#, occupies 6 bytes of space

Extended rowID, composed of data_object_id#+rfile#+block#+row#, occupies 10 bytes space

Ii. format of the ROWID

Sql> Select rowid,t.* from dept t where t.deptno=10;

ROWID DEPTNO dname LOC

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

AAAO0FAAFAAAALMAAA ACCOUNTING NEW YORK/*

Aaao0f-aaf-aaaalm-aaa

Object Number (6 characters) file number (3 characters) block number (6 characters) line number (3 characters)

Third, view rowid information and related demonstrations

1, view the heap table rowID and obtain ROWID information

Sql> select rowid,dept.* from dept; --> view rowid for all records in the table dept

ROWID DEPTNO dname LOC

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

AAAO0FAAFAAAALMAAA ACCOUNTING NEW YORK

Aaao0faafaaaalmaab DALLAS

AAAO0FAAFAAAALMAAC SALES CHICAGO

Aaao0faafaaaalmaad OPERATIONS BOSTON

Sql> Select object_name,object_id from dba_objects where object_name= ' DEPT ' and owner= ' SCOTT ';--> View Object IDs

object_name object_id

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

DEPT 60703

Sql> Select Dbms_rowid.rowid_object (ROWID) object_id,--> use Dbms_rowid package to obtain ROWID decimal information

2 Dbms_rowid.rowid_relative_fno (ROWID) file_id,

3 Dbms_rowid.rowid_block_number (ROWID) block_id,

4 dbms_rowid.rowid_row_number (ROWID) num

5 from Dept;

object_id file_id block_id NUM--> Here you can see the corresponding object number, file number, block number, and line number

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

60703 5 2406 0

60703 5 2406 1

60703 5 2406 2

60703 5 2406 3

sql> Col file_name Format A50

Sql> Select File_id,file_name from Dba_data_files where file_id=5; --> the location of the data file where the object resides through the file ID

file_id file_name

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

5/u02/database/cnmmbo/oradata/cnmmbo_system_tbl.dbf

Sql> Select rowID,--> this query is separated according to the ROWID definition format rowid

2 substr (rowid,1,6) "Object",

3 substr (rowid,7,3) "File",

4 substr (rowid,10,6) "Block",

5 substr (rowid,16,3) "Row"

6 from Dept;

ROWID Object File Block row

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

AAAO0FAAFAAAALMAAA aaao0f AAF Aaaalm AAA

Aaao0faafaaaalmaab aaao0f AAF Aaaalm AAB

AAAO0FAAFAAAALMAAC aaao0f AAF Aaaalm AAC

Aaao0faafaaaalmaad aaao0f AAF Aaaalm AAD

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/