Oracle Data Types and storage methods (6)

Part 6 ROWID
ROWID is the address of a row in the database. It is used to record some attributes of data storage, including: the data file where the record is stored (file #), the database object (obj #), the data block number (block_no #) and the row number in the table. These attributes constitute the ROWID of Oracle.
We need to note that there is no column in the data table to specifically record ROWID.
In addition, there is a UROWID used for the table, which is a representation of the primary key of the row. It is generated based on the primary key. It is generally used by the index organization table. The index organization table does not have ROWID.
No specific column is recorded in the data table, whether it is ROWID or UROWID.
These two types are called pseudo columns.
SQL> create table test_rowid (id number (38 ));
Table created
SQL> insert into test_rowid values (1 );
1 row inserted
SQL> select rowid, id from test_rowid;
ROWID
---------------------------------------------------------
AAAKsAAAEAAAAC + AAA 1
Because ROWID can uniquely identify a record, the ROWID value is stored in the index and accessed through the index. In fact, the ROWID is obtained from the index, and then the records in the data table are located based on the ROWID.
However, when the table is partitioned and moved, the index needs to be rebuilt because the storage location has changed and the ROWID in the index cannot be located again.
The ROWID of ORACLE is constantly changing.
In ORACLE 6, ROWID uses 6bit to represent the file number.
In ORACLE 8, ROWID is composed of FFFF. BBBBBBBB. RRRR. Occupies 6 bytes.
10bit file #, 22bit block #, 16bit row #
In ORACLE 9, Oracle introduced the concept of data object number dataobj # For ROWID #.
Now the ROWID format is OOOOOO. FFF. BBBBBB. RRR. The latest ROWID is Base64 encoded, with a total of 18 bits representing the number of 80 bits in binary. Among them, O indicates the data object number, F indicates the file number, B indicates the block number, and R indicates the row number.
32 bit dataobj # + 10bit rfile #, + 22 bit block # + 16bit row #
Previously, the ROWID remains unchanged, but now the ROWID changes. For example:
Move a row from one partition to another
Use the flashback table command to restore a data table to a previous time point.
Perform operations on partitions, such as moving, splitting, and merging.
Segment contraction
These operations change the ROWID, so we should not use the ROWID as a unique identifier. Instead, a separate column is used as the primary key to uniquely identify a data row. In addition, primary key constraints can enable integrity of reference. ROWID cannot.
I used ROWID sorting to display data in the write order. This can be done in most cases, but it is not feasible to perform operations on partitions after the database is maintained.
Therefore, separate columns should be used to record the data writing sequence.
The main purpose of the ROWID type is to quickly point to a row when interacting with a database. For example, update a row using ROWID. You can quickly find a row of records without indexing. In addition, you can quickly verify the row data.