Shulou(Shulou.com)06/01 Report--

Oracle rowid

One: rowid composition

Https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF00213

Https://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns008.htm#SQLRF00254

(1) The data object number of the object

(2) The data block in the data file in which the row resides

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

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

Second: important uses of rowid

Https://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns008.htm#SQLRF00254

Rowid values have several important uses:

(1) They are the fastest way to access a single row.

(2) They can show you how the rows in a table are stored.

(3) They are unique identifiers for rows in a table.

Three: rowid restriction

Small Datafile

Max (number of data files in a tablespace) = (2 ^ 10)-1 ^ 1023

Max (number of blocks in a data file) = 2 ^ 22 = 4194304blocks 4M (block)

Max (number of rows in a block) = 2 ^ 16 = 65536 rows 64k (rows)

Max (number of object in a database) = 2 ^ 32 = 429496729614G (objects)

Bigfile Datafile

Max (number of data files in a tablespace) = 1

Max (number of blocks in a data file) = 2 ^ (22 blocks 10) = 4294967296 blocks 4G (block)

Max (number of rows in a block) = 2 ^ 16 = 65536 rows 64k (rows)

Max (number of object in a database) = 2 ^ 32 = 429496729614G (objects)

The tests are as follows:

(1) create test data

SQL > create tablespace chenjch_tbs datafile'/ u01 size size autoextend on maxsize 1G

SQL > create user chenjch identified by a default tablespace chenjch_tbs

SQL > grant connect,resource,dba to chenjch

SQL > create table T1 as select * from scott.emp

SQL > create table T2 as select * from scott.dept

(2) View the rowid information of T1 table

SQL > SELECT rowid,empno from T1 an order by empno

SQL >

Select substr (rowid, 1,6) "object"

Substr (rowid, 7, 3) "file"

Substr (rowid, 10,6) "block"

Substr (rowid, 16,3) "row"

From t1

-take out any row of rowid

AAAVV9 AAF AAAACD AAC

-calculate the corresponding obj#,rfile#,block#,row# through rowid

(1) obj#=AAAVV9=21* 64 ^ 2 + 2116 ^ 61 = 87421

(2) rfile#=AAF= 5

(3) block#=AAAACD=2*64+3= 131

(4) row#=AAC= 2

-the corresponding obj#,rfile#,block#,row# can also be obtained through dbms_rowid conversion.

SQL >

Se lect dbms_rowid.rowid_object (rowid) object_id

Dbms_rowid.rowid_relative_fno (rowid) file_id

Dbms_rowid.rowid_block_number (rowid) block_id

Dbms_rowid.rowid_row_number (rowid) row_number

ROWID

Empno

From T1

Order by empno

(3) rowid and restrictions

Small Datafile

Rowid uses 10 byte to store = 8*10=80bit

Where:

Obj# occupies 32bit

Rfile# occupies 10bit

Block# occupies 22bit

Row# occupies 16bit.

Max (number of data files in a tablespace) = (2 ^ 10)-1 ^ 1023

Max (number of blocks in a data file) = 2 ^ 22 = 41943044blocks 4M (block)

Max (number of rows in a block) = 2 ^ 16 = 65536 rows 64k (rows)

Max (number of object in a database) = 2 ^ 32 = 429496729614G (objects)

Bigfile Datafile

Rowid uses 10 byte to store = 8*10=80bit

Where:

Obj# occupies 32bit

Rfile# occupies 0bit

Block# occupies 32bit

Row# occupies 16bit.

Max (number of data files in a tablespace) = 2 ^ 0 = 1

Max (number of rows in a block) = 2 ^ 16 = 65536 rows 64k (rows)

Max (number of object in a database) = 2 ^ 32 = 429496729614G (objects)

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.