Oracle ROWID Use

rowID is the detailed address of the data, the location of the specific data of a row can be quickly located by Rowid,oracle.

rowID can be divided into two kinds of physical rowid and logic rowID . The rowid in a normal heap table is a physical rowid, and the ROWID of an indexed organization table (IOT) is a logical rowid. Oracle provides a UROWID data type that supports both physical and logical rowid. This paper focuses on physical rowid

The physical rowid is divided into two formats: Extended ROWID (extended rowid) and restricted rowid (restricted rowid) . Restricting rowID is primarily oracle7 the previous ROWID format, which is now no longer used, and is reserved for compatibility only. So the reference to physical rowid in this article generally refers to the extended ROWID format.

The main content of this article:

Display form of 1.Rowid

2. How to get obj#,rfile#,block#,row# from ROWID calculation

3. How to get rowid from obj#,rfile#,block#,row# calculation

Internal storage format of 4.Rowid

rowID stored in 5.Index

Display form of 1.Rowid

Our rowid from rowID pseudo joins Select is based on Base64 encoding, a total of 18 bits, divided into 4 parts:

Oooooofffbbbbbbrrr

which

Oooooo: The six-bit represents the data object ID, which determines the segment based on the object ID. For the difference between the data object ID and the object ID, please refer to the HTTP://WWW.ORAWH.COM/INDEX.PHP/ARCHIVES/62

FFF: Three-bit indicates relative file number. The absolute file number can be obtained according to the relative file number, thus determining the datafile. Refer to http://blog.itpub.net/post/330/22749 for relative file number and absolute file number

BBBBBB: Six bits represents the data block number. The data block number here is relative to the datafile, not to the tablespace number.

RRR: Three-bit indicates row number.

Oracle provides DBM_ROWID to perform some conversion calculations for ROWID.

Sql> CREATE TABLE Test (ID int,name varchar2 (30));

Table created.

sql> INSERT INTO test values (1, ' a ');

1 row created.

Sql> commit;

Commit complete.

Sql> select rowID from test;

ROWID
------------------
Aaagbeaahaaaab8aaa

Sql> Select Dbms_rowid.rowid_object (ROWID) obj#,
2 Dbms_rowid.rowid_relative_fno (ROWID) rfile#,
3 Dbms_rowid.rowid_block_number (ROWID) block#,
4 Dbms_rowid.rowid_row_number (ROWID) row#,
5 dbms_rowid.rowid_to_absolute_fno (rowID, ' SYS ', ' TEST ') file#
6 from Test;

obj# rfile# block# row# file#
----------- ------------ ------------- ---------- ----------
26308 7 124) 0 7

2. How to get obj#,rfile#,block#,row# from ROWID calculation

ROWID is Base64 encoded with a~z a~z 0~9 +/A total of 64 characters. A means that 0,b indicates that 1,......,a represents a 26,......,0 representation that 52,......,+ represents a 62,/that 63 can be seen as a 64-decimal number.

So

obj#=aaagbe=6*64^2+27*64+4=26308

Rfile#=aah=7

block#=aaaab8=64+60=124

Row#=aaa=0

3. How to get rowid from obj#,rfile#,block#,row# calculation

In fact, the decimal number is converted to 64 binary number, of course, the rule from the binary conversion is relatively simple point.

The binary number from right to left, 6 bit a group, and then the 6 bit group to the 10 binary number, that is, a~z a~z 0~9 +/The position of the 64 characters (starting from 0), replaced by Base64 characters.

obj#=26308=110 011011 000100=6 4=g b E, top up into 6-bit base64 code, left 0, that is, a, the result is Aaagbe

Rfile#=7=111=7=h, 3-bit, get aah

Block#=124=1 111100=1 60=b 8, topped up into 6 bits, got AAAAB8

row#=0,3 bit AAA

Together, it's aaagbeaahaaaab8aaa.

4. ROWID's internal storage format

Although the rowid that we select from the ROWID pseudo-column are displayed in Base64 characters, the binary representation of the original value is stored inside the Oracie. An extended rowid is stored in 10 bytes, with a total of 80bit, of which obj#32bit,rfile#10bit,block#22bit,row#16bit. Therefore, the relative file number can not exceed 1023, that is, a tablespace data file can not exceed 1023 (there is no file number 0 file), a datafile can only have 2^22=4m block, a block can not exceed 2^16=64k rows of data. There cannot be more than 2^32=4G object in a database.

Sql> Select Dump (rowid,16) from test;

DUMP (rowid,16)
--------------------------------------------

typ=69 len=10:0,0,66,c4,1,c0,0,7c,0,0

00000000 00000000 01100110 11000100 00000001 11000000 00000000 01111100 00000000 00000000

The rightmost 16bit is row#=00000000 00000000=0

The next 22bit is block#=000000 00000000 01111100=124

Next 10bit for rfile#=00000001 11=7

The next 32bit is obj#=00000000 00000000 01100110 11000100=26308

5. rowID stored in Index

A. General B-tree index

Sql> CREATE index ix_test on test (ID);

Index created.

Sql> Select file_id,block_id from dba_extents where segment_name= ' ix_test ' and owner=user;

file_id block_id
---------- ----------
7 129

---because it is a ASSM table space, remove the head of 3 blocks
sql> alter system dump DATAFILE 1 Block 132;

System altered.

Get the trace file contents as follows (omit extraneous content):
ROW#0[8024] Flag:-----, lock:0
Col 0; Len 2; (2): C1---index key data id=1
Col 1; Len 6; (6): C0 7c, corresponding to the ROWID record of the---XX
-----End of leaf block dump-----
End dump data blocks Tsn:7 file#: 7 minblk maxblk 132

The rowid saved in the normal index does not include obj#, but the global index of the partitioned table includes obj#, because the partition table includes multiple segment, each segment may be in a different datafile, depending on the obj# of the table It is not possible to determine the rowid of the index key (rfile# is not determined).

C0 7c 00 00 conversion to binary 000000001 11000000 00000000 01111100 00000000 00000000

Right 8bit row#=0

Next 22bit block#=000000 00000000 01111100=124

Next 10bit rfile#=000000001 11=7

B. Unique index

sql> DROP Index ix_test;

Index dropped.

Sql> Create unique index ix_test on test (ID);

Index created.

Sql> Select file_id,block_id from dba_extents where segment_name= ' ix_test ' and owner=user;

file_id block_id
---------- ----------
7 129

sql> alter system dump DATAFILE 1 Block 132;

System altered.

Get the trace file contents as follows:

ROW#0[8025] Flag:-----, lock:0, Data: (6): rowID records corresponding to the 7c---of C0 xx
Col 0; Len 2; (2): C1---index key data id=1
-----End of leaf block dump-----
End dump data blocks Tsn:7 file#: 7 minblk maxblk 132

Get rowID for C0 7c 00 00, the specific conversion calculation and the same as before, will not repeat.

Base64 Code Description
The BASE64 encoding requires the conversion of 3 8-bit bytes (3*8=24) into 4 6-bit bytes (4*6=24), followed by 6 two in front of 0 bits, forming a 8-bit byte form. If the remaining characters are less than 3 bytes, then 0 is populated with the output character using ' = ', so there may be 1 or 2 ' = ' At the end of the encoded text output.

To ensure the output of the encoded bit-readable characters, BASE64 developed an encoding table for uniform conversion. The size of the encoded table is 2^6=64, which is also the origin of the Base64 name.

BASE64 Coding Table

Code Value	character		Code Value	character		Code Value	character		Code Value	character
0	A		16	Q		32	G		48	W
1	B		17	R		33	H		49	X
2	C		18	S		34	I		50	Y
3	D		19	T		35	J		51	Z
4	E		20	U		36	K		52	0
5	F		21st	V		37	L		53	1
6	G		22	W		38	M		54	2
7	H		23	X		39	N		55	3
8	I		24	Y		40	O		56	4
9	J		25	Z		41	P		57	5
10	K		26	A		42	Q		58	6
11	L		27	B		43	R		59	7
12	M		28	C		44	S		60	8
13	N		29	D		45	T		61	9
14	O		30	E		46	U		62	+
15	P		31	F		47	V		63	/

Oracle ROWID Use