Summary of the storage experience of the logical Oracle ROWID

The following articles mainly introduce the storage formats of Oracle ROWID data types. We all know that Oracle related documents generally do not provide an introduction to the encoding rules of logical Oracle ROWID, in addition, it is difficult to introduce encoding rules through DUMP results.

Therefore, this article only briefly discusses the storage of the logical Oracle ROWID.

The following is an example.

 
 

  
  
SQL> create table test_index (id number primary key, name varchar2(20)) organization index;  
 
 

The table has been created.

 
 

  
  
SQL> insert into test_index values (1, 'a');  
 
 

One row has been created.

 
 

  
  
SQL> commit;  
 
 

Submitted.

 
 

  
  
SQL> col dump_rowid format a60  
  
  
SQL> select rowid, dump(rowid) dump_rowid from test_index;   
  
  
ROWID DUMP_ROWID  
 
 

* BAFAB4wCwQL + Typ = 208 Len = 10: 7,140, 2,193, 2,254

The first two digits of the DUMP result of the logical ROWID are 2 and 4, and the last one is 254. I have not found any other situations.) because the logical ROWID is related to the value of the primary key, therefore, the length is not fixed, so it should be used to indicate the start and end.

The four or 3rd bits are the same as the physical ROWID, indicating that the data file number of the relative tablespace is multiplied by the value of 64.

5th and 6 bits indicate the number of blocks in the data file.

Starting from 7th bits to the second-to-last position of the DUMP result, which indicates the value of the primary key. The first is the length of the first field in the primary key. Here it is 2, and then the value of the primary key. Because it is of the NUMBER type, 193,2 indicates the value 1. For a primary key consisting of multiple fields, the first field is followed by the length of the second field, and then the value of the second field .......

 
 

  
  
SQL> select (1*256 + 64)/64 from dual;   
  
  
(1*256+64)/64  
  
  
5   
  
  
SQL> select 7*256 + 140 from dual;   
  
  
7*256+140  
  
  
1932   
  
  
SQL> alter system dump datafile 5 block 1932;   
  
  
 
 
 

The system has been changed.

Find the corresponding dump file to find the inserted record.

 
 

  
  
Dump file f:oracleadmintest4udumptest4_ora_3828.trc  
  
  
Thu Dec 23 00:17:53 2004  
  
  
ORACLE V9.2.0.4.0 - Production vsnsta=0 
  
  
vsnsql=12 vsnxtr=3 
  
  
Windows 2000 Version 5.1 Service Pack 1, CPU type 586  
  
  
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production  
  
  
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options  
  
  
JServer Release 9.2.0.4.0 - Production  
  
  
Windows 2000 Version 5.1 Service Pack 1, CPU type 586  
  
  
Instance name: test4   
  
  
Redo thread mounted by this instance: 1   
  
  
Oracle process number: 9   
  
  
Windows thread id: 3828, image: ORACLE.EXE   
  
  
 
  
  
*** 2004-12-23 00:17:53.361  
  
  
*** SESSION ID:(8.82) 2004-12-23 00:17:53.301  
  
  
Start dump data blocks tsn: 5 file#: 5 minblk 1932 maxblk 1932  
  
  
buffer tsn: 5 rdba: 0x0140078c (5/1932)  
  
  
scn: 0x0000.00e9f122 seq: 0x01 flg: 0x02 tail: 0xf1220601  
  
  
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data  
  
  
Block header dump: 0x0140078c  
  
  
Object id on Block? Y  
  
  
seg/obj: 0x1e48 csc: 0x00.e9f113 itc: 2 flg: E typ: 2 - INDEX  
  
  
brn: 0 bdba: 0x1400789 ver: 0x01  
  
  
inc: 0 exflg: 0  
  
  
Itl Xid Uba Flag Lck Scn/Fsc  
  
  
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000  
  
  
0x02 0x0005.008.000000e7 0x00800226.005c.24 --U- 1 fsc 0x0000.00e9f122  
  
  
Leaf block dump  
  
  
header address 71963236=0x44a1264 
  
  
kdxcolev 0  
  
  
KDXCOLEV Flags = - - -  
  
  
kdxcolok 0  
  
  
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y 
  
  
kdxconco 1  
  
  
kdxcosdc 0  
  
  
kdxconro 1  
  
  
kdxcofbo 38=0x26 
  
  
kdxcofeo 8026=0x1f5a 
  
  
kdxcoavs 7988  
  
  
kdxlespl 0  
  
  
kdxlende 0  
  
  
kdxlenxt 0=0x0 
  
  
kdxleprv 0=0x0 
  
  
kdxledsz 0  
  
  
kdxlebksz 8036  
  
  
row#0[8026] flag: K----, lock: 2  
  
  
col 0; len 2; (2): c1 02  
  
  
tl: 5 fb: --H-FL-- lb: 0x0 cc: 1  
  
  
col 0: [ 1]  
  
  
Dump of memory from 0x044A31C7 to 0x044A31C8  
  
  
44A31C0 61010100 [...a]   
  
  
----- end of leaf block dump -----  
  
  
End dump data blocks tsn: 5 file#: 5 minblk 1932 maxblk 1932   
  
  
 
 
 

We can see that the physical location of the record can be located based on the 3, 4, 5, and 6 digits of the DUMP result.

Note that the index organization table stores data in the order of primary keys. Therefore, inserting, updating, and deleting data may change the physical location of a record, in this case, the data in DATAFILE and BLOCK in Oracle ROWID may not be able to correctly locate the physical location of the record.

When you access an index to organize a table based on the Logical Oracle ROWID, the corresponding BLOCK is first found based on the DATAFILE and BLOCK information to check whether the data is in this BLOCK. If not, you can use the primary key information in the logical ROWID to scan the index and find this record. This is the physical guess mentioned in the Oracle document.

The following is an example of a joint primary key consisting of a string and a date.

 
 

  
  
SQL> create table test_index2 (id char(4), time date,   
  
  
2 constraint pk_test_index2 primary key (id, time)) organization index;   
 
 

The table has been created.

 
 

  
  
SQL> insert into test_index2 values ('1', sysdate);  
 
 

One row has been created.

 
 

  
  
SQL> col dump_rowid format a75  
  
  
SQL> select rowid, dump(rowid) dump_rowid from test_index2;   
  
  
ROWID DUMP_ROWID  
  
  
*BAFAB5QEMSAgIAd4aAwXASMT/g Typ=208 Len=20: 2,4,1,64,7,148,4,49,32,32,32,7,120,104,12,23,1,35,19,254   
 
 

It can be seen that 7th bits are the length of the field id 4, followed by the ASCII code of string 1 and three spaces, which is the storage format of the string, followed by 7 is the length of the field time, the last seven digits are the date storage format. In the logical Oracle ROWID, the storage formats of values, characters, and dates are the same as their storage formats.

Generally, one digit is used to indicate the length, but if the length exceeds 12716 hexadecimal DUMP, the result is 7F), the length is represented by two digits. The first digit starts with 8. This 8 is only a flag, indicating that the length field is represented by two digits. For example, the length of 128 represents 8080 bits, and the maximum supported value of 3800 represents 8ED8.