Detailed explanation of the ins and outs of Oraclerowid

Rowid is literally interpreted as a row ID. In oracle, rowid is the fastest and most effective way to locate records. So how does rowid locate the record in oracle? And

Rowid is literally interpreted as a row ID. In oracle, rowid is the fastest and most effective way to locate records. So how does rowid locate the record in oracle? And

Rowid is literally interpreted as a row ID. In Oracle, rowid is the fastest and most effective way to locate records. So how does rowid locate the record in oracle? And why is it the most effective way? With these problems, let's unveil the mystery of rowid step by step.

First, let's take a look at how to obtain the record rowid In the table: using the rowid pseudo Column

SQL> select rowid, id from ppp;

ROWID
----------------------------
AAAS5VAAEAAAAemAAC 3
AAAS5VAAEAAAAemAAD 9
AAAS5VAAEAAAAemAAE 7

We know that oracle has multiple logical tablespaces, and each tablespace contains multiple data files. The data files correspond to files on the Operating System (similar to the asm situation, not to be considered ), the data file contains several data blocks. Our table records are stored in the data blocks. Therefore, if we know the data files, data blocks, and offsets in the blocks stored in a record, we can quickly read the records and present them to users. Rowid can help us achieve this, because the rowid string contains information about the data file, data block, and the address recorded in the block.

Before 8i, oracle adopted the Restricted rowid (Restricted rowid). The Restricted rowid is the rowid for the entire database range, which consists of three parts: the data file number, the block number and the offset of the record in the block. The restricted rowid occupies 6 bytes of storage space, where the data file number occupies 10 bits, the data block number occupies 22 Bits, And the offset is the number of lines recorded in the data block occupies 16 bits. Here we can also see the database before 8i:

Each database can contain a maximum of 1022 files (reserved for 2 files)

Each file can have a maximum of 4 MB data blocks.

Each block can store up to 64 K Records.

Restricted rowids are stored in binary format at the underlying layer. varchar2 and hex are used in the format of bbbbbbbbbb. RRRR. FFFF (block #. row #. file #). For example:

SQL> select dbms_rowid.rowid_to_restricted ('aaas5vaaeaaaaemaae ', 0) from dual;

DBMS_ROWID.ROWID_T
------------------
0007a6. 0004.0004

So far, the situation has been very clear, but with the development of oracle, we need to break through some restrictions, such as a single database with a maximum of 1022 data files, at the same time, we also need to maintain the backward compatibility of the database and fully consider some features of the database, such as tablespace migration. In this case, it is not enough to extend the storage length of the rowid restricted by the data file number. For example, we can increase the storage space occupied by the data file number from 10 bits to 20 bits, although more data files can be stored in a single database, it increases the difficulty of backward compatibility (because the physical storage format has changed), and when table space migration is used, if you migrate data from an earlier version to a later version than 8i, You need to scan the entire migrated tablespace to modify the rowid information stored in the tablespace, this is obviously contrary to the original intention of table space migration (copying files and importing metadata information to import table space.

In order to achieve the above purposes, oracle introduced the concept of relative file numbers. The main idea of this method is to change the data file numbers in the previous rowid to refer to the fact that the entire database range is I, change the reference range to a tablespace. That is, a file numbered 4 is no longer a data file numbered 4 in the database, but a data file numbered 4 in a tablespace. In this way, we can change the processing logic without changing the physical storage format (for example, parsing the first 10 bits as the tablespace relative file number rfn, instead of file_id, you can use the data dictionary view to expand the database.

SQL> select file_id, relative_fno from dba_data_files;

FILE_ID RELATIVE_FNO
----------------------
4
3 3
2 2
1 1
5 5
6 6
7 6
8
9 9

From this we can see that file_id and relative_fno are one-to-one equal. Otherwise, when the number of data files does not exceed 1022, the oracle database should try to keep file_id and relative_fno the same, after more than 1022 data files exist, oracle will ensure that the file_id is unique throughout the database, and relative_fno is unique in a single tablespace.

Then there will be a problem. How Does oracle, a data file with the same and relative file numbers in different tablespaces, distinguish them? To solve this problem, oracle adds the DATA_OBJECT_ID information on the basis of the original 6 byte rowid to form the extended rowid, which consists of four parts: data_object_id, rfn, block #, row #. Through the combination of data_object_id and data dictionary view, oracle can quickly convert rfn to file_id, so as to accurately locate rows.

Taking the storage format of indexes in 11 GB as an example, the following is a summary:

Normal table:

-- Normal index: 6 bytes
Row #0 [8020] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 02
Col 1; len 6; (6): 01 00 05 e3 00 00
Row #1 [8008] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 03
Col 1; len 6; (6): 01 00 05 e3 00 01
Row #2 [7996] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 04
Col 1; len 6; (6): 01 00 05 e3 00 02

-- Global partition:
Row #0 [8020] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 02
Col 1; len 6; (6): 01 00 05 e3 00 00
Row #1 [8008] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 03
Col 1; len 6; (6): 01 00 05 e3 00 01
Row #2 [7996] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 04
Col 1; len 6; (6): 01 00 05 e3 00 02
----- End of leaf block dump -----

Partition Table:

-- Global partition: 10 bytes of Partition
Col 0; len 2; (2): c1 02
Col 1; len 10; (10): 00 01 2e 55 01 00 07 a6 00
Row #1 [7984] flag: ------, lock: 0, len = 16
Col 0; len 2; (2): c1 04
Col 1; len 10; (10): 00 01 2e 55 01 00 07 a6 00 02
Row #2 [7968] flag: ------, lock: 0, len = 16
Col 0; len 2; (2): c1 08
Col 1; len 10; (10): 00 01 2e 55 01 00 07 a6 00 04
Row #3 [7952] flag: ------, lock: 0, len = 16
Col 0; len 2; (2): c1 0a
Col 1; len 10; (10): 00 01 2e 55 01 00 07 a6 00 03