Some content about RowID
Author: Liuying Bo
Time: 2004-6-12
Mail: liuyingbo@126.com, please correct
Reprint, please indicate the source and the author
This article discusses RowID, ie, extended RowID: Extended RowID:
1. RowID introduction
There is a sensory understanding of RowID first:
SQL> SELECT ROWID from Bruce_Test WHERE ROWNUM <2; ROWID -------------------------- Aaabnlaafaaaaaapa
The RowID format is as follows:
Data Object Number File Number Block Number
OOOOOO FFF BBBBBB RRR
We can see that it can be learned from the above RowID:
AAABNL is the data object number
AAF is the relevant file number
AAAAAP is a block number
AAA is the number
How to get specific decimal coding values based on these numbers, this is a problem that is often encountered. Here, it is necessary to understand that RowID is based on 64-bit encoded 18 characters (data object number (6) file number (3) block number (6) line number (3) = 18 bits), where
A-Z <==> 0 - 25 (26) A-Z <==> 26 - 51 (26) 0-9 <==> 52 - 61 (10) / <==> 62 - 63 (2)
A total of 64 bits, I understand this, you can calculate the coding value of 10, and the calculation formula is as follows:
D * (b ^ P)
Where: b is the base, here is 64, P is from right to left, 0 started number
For example: the example above
The file number AAF, the specific calculation should be:
5 * (64 ^ 0) = 5;
0 * (64 ^ 1) = 0;
0 * (64 ^ 2) = 0;
The file number is 0 0 5 = 5
What I just mentioned is RowID display: based on 64-bit encoded 18 characters display, the ROWID storage method is: 10 bytes, namely 80-bit storage, where data object numbers need 32 bits, the relevant file number requires 10 items The block number requires 22, and the bit number requires 16 digits, whereby we can draw:
32bit Object Number, up to 4G objects per database
10bit File Number, up to 1022 files per object (2 file reservations)
22bit Block Number, up to 4M block per file
16bit's Row Number, each Block has up to 64K ROWS
2. RowID related useful SQL
The SQL of the 64-bit encoding corresponding value of the simplest ROWID-based display mode is:
SELECT ROWID,
Substr (RowID, 1, 6) "Object",
Substr (RowID, 7, 3) "file",
Substr (RowID, 10, 6) "Block",
Substr (RowID, 16, 3) "ROW"
From Tablename;
Owid Object File Block Row
---------------------------------------------- ----
Aaabc4aadaaagluaaaa Aaabc4 Aad Aaaglu AAA
AAABC4AADAAAAGLUAAB AAABC4 AAD AAAAGLU AAB
AAABC4AADAAAGLUAAC AAABC4 AAD AAAAGLU AAC
AAABC4AADAAAGLUAAD AAABC4 AAD AAAGLU AAD
AAABC4AADAAAGLUAAAAAAAABC4 AAD AAAGLU AAE
With DBMS_ROWID this package, you can directly get the information contained in the specific ROWID:
select 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) num from bruce_t where rownum <5;
Object_id file_id block_id num
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5944 3 25300 0
5944 3 25300 1
5944 3 25300 2
5944 3 25300 3
Some functions using RowID RowidTochar (RowID): Convert ROWID to StringChartorowid ('RowId_String'): Convert String to RowID
In addition, some functions written by themselves: (The following function is the netizen Eygle)
Create Or Replace Function Get_RowID
(l_rowid in varchar2)
Return varcha2
IS
LS_MY_ROWID VARCHAR2 (200);
RowId_type number;
Object_number number;
RELATIVE_FNO NUMBER;
Block_number number;
Row_Number Number;
Begin
DBMS_ROWID.ROWID_INFO (L_RowID, RowId_Type, Object_Number, Relative_fno, Block_Number, Row_Number);
LS_MY_ROWID: = 'Object # IS:' || to_char (Object_number) || CHR (10) ||
'Relative_fno is:' || to_CHAR (Relative_Fno) || CHR (10) ||
'Block Number is:' || to_char (block_number) || CHR (10) ||
'Row Number IS:' || to_CHAR (Row_Number);
RETURN LS_MY_ROWID;
END;
/
The functions above are as follows:
SQL> SELECT GET_ROWID (ROWID), Name from Bruce_t; Get_Rowid (RowID) Name
-------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------
Object # is: 5944 brucelau
Relative_fno is: 3
Block Number IS: 25300
Row Number IS: 0
Object # is: 5944 mabeltang
Relative_fno is: 3
Block Number IS: 25300
Row Number IS: 1
