Detailed description of ROWID in Oracle 10g

Detailed description of ROWID in Oracle 10g

First use select * from v $ version; to query the current version of the Oracle database
 
SQL> select * from v $ version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Prod
PL/SQL Release 10.2.0.3.0-Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0-Production
NLSRTL Version 10.2.0.3.0-Production
 
First, execute a query:
 
SQL> select rowid, employee_id, first_name, last_name, job_id
2 from employees
3 where rownum <11;
 
ROWID EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
------------------------------------------------------------------------------------
AAAMieAAFAAAABUAAA 198 Donald OConnell SH_CLERK
AAAMieAAFAAAABUAAB 199 Douglas Grant SH_CLERK
AAAMieAAFAAAABUAAC 200 Jennifer Whalen AD_ASST
AAAMieAAFAAAABUAAD 201 Michael Hartstein MK_MAN
AAAMieAAFAAAABUAAE 202 Pat Fay MK_REP
Aaamieaafaaaabuaaf203 Susan Mavris HR_REP
AAAMieAAFAAAABUAAG 204 Hermann Baer PR_REP
AAAMieAAFAAAABUAAH 205 Shelley Higgins AC_MGR
AAAMieAAFAAAABUAAI 206 William Gietz AC_ACCOUNT
Aaamieaafaaaabyaaaaa 100 Steven King AD_PRES
 
10 rows have been selected.
 
The value of ROWID displayed in the query result is displayed.

Oracle rowid

Basic Oracle Tutorial: rowid

Summary of ROWNUM usage in Oracle, differences between ROWNUM and rowid

Rowid captured by index suspect

Oracle uses rownum and rowid for paging
 

The ROWID of Oracle uniquely identifies a record in the table, which is the physical address of the data stored in the database.
The ROWID pseudo column value of Oracle is not stored in the database. Oracle calculates the query value. Oracle's physical extended ROWID has 18 bits, each of which uses 64-bit encoding, respectively using ~ Z, ~ Z, 0 ~ 9, +,/represents a total of 64 characters. A Indicates 0, B Indicates 1 ,...... Z indicates 25, a indicates 26 ,...... Z indicates, 0 indicates 52 ,......, 9 indicates 61, + indicates 62,/indicates 63.
You can use the DBMS_ROWID package to convert the ROWID value to meaningful information.
 
SQL> select dbms_rowid.rowid_relative_fno (rowid), employee_id
2 from employees
3 where employee_id = 200;
 
DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) EMPLOYEE_ID
-----------------------------------------------
5 200
 
Run the following query to obtain information about the physical data files stored in the data:
 
SQL> col tablespace_name format a20;
SQL> col file_name format a80;
SQL> select file_name, tablespace_name from dba_data_files where relative_fno = 5;
 
FILE_NAME TABLESPACE_NAME
----------------------------------------------------------------------------------------------------
E: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ KEYMENDB \ EXAMPLE01.DBF EXAMPLE
 

The value of ROWID can be used in SELECT and WHERE clauses of SQL statements. In most cases, ROWID uniquely identifies a row. However, rows may be stored in different tables in the same cluster and therefore contain rows with the same ROWID.
 
To understand ROWID, you must first understand its two aspects: ROWID characteristics and ROWID structure.
 

1. Features of ROWID
(1) rowid is a pseudo column used to ensure the uniqueness of the row in the table. It does not indicate the physical location of the trip, but can be used to locate the row.
(2) rowid is a set of established values stored in the index (when the row is determined ). We can select it like a normal column in a table.
(3) using rowid is the fastest way to access a row in the table.
(4) The rowid needs 10 bytes for storage and is displayed as an 18-Bit String.
 
Ii. ROWID Structure
The rowid structure is as follows:
 
Data object number (6-Bit String) relative file number (3-Bit String) block number (6-Bit String) row number (3-Bit String), such as AAAMieAAFAAAABUAAC
 
64-encoded physical address, the encoding character is A-Z, a-z, 0-9, +, and /.
 
Consists of four parts: OOOOOOFFFBBBBBBRRR (obj # file # block # row #)
 
OOOOOO -- data object number
 
FFF -- the data file number relative to the tablespace
 
BBBBBB-block number
 
RRR --- row number

Rowid changes when the following conditions occur:
(1) After moving a table's tablespace
(2) perform EXP/IMP on a table