最近讀到ROWID這東西… 費了我不少時間啊 orz。
Introduction
ROWID顧名思義，可以透過他來識別/定位資料庫裡的任何一個row。他並非真正的欄位(column)，但是在每個表(table)裡，我們都可以下select ROWID來取得他的值。
Format
先簡單介紹一下Extended ROWID的構成 (在Oracle7以及更早以前的版本是所謂的Restricted ROWID，這裡不討論)，ROWID是經過base-64編碼後以18個字元呈現，需用到最大10 byte的儲存空間。
- Data object #: 6個字元。白話一點說，就是row所在的table。以oracle的邏輯架構來看，table算是一個segment，而透過segment我們可以知道這個row在哪個Tablespace內
- Relative file #: 3個字元。定位row實際上是在哪個datafile內
- Block #: 6個字元。定位row所在的data block
- Row #: 3個字元。定位row本身
ROWID
SQL> select rowed from hr.jobs where job_id='XXX';
實際上select出的ROWID，大概是長這樣 → AAAgwuAAKAAAl7hAAR
拆解各部份套到上面圖示，會是這樣:
原本以為到DBA_OBJECTS內找到相對應的'DATA_OBJECT_ID'欄位也會是"AAAgwu"，結果大錯特錯。
SQL> select data_object_id from dba.objects where owner='HR' and object_name='JOBS';
得到的值竟然是一個數字!!! "134190"!!!! what!?!?
嗯… 因為我上面有提到，ROWID的18個字元，是經過base-64編碼的… 必須再處理過，才會是134190這個data object number。
BASE64 to Decimal
其實sys下也有現成的package可用: DBMS_ROWID (This package provides procedures to create ROWIDs and to interpret their contents)，只要把rowid丟進DBMS_ROWID.ROWID_INFO內(外加其他幾個承接結果的變數)，就可以得到每一段轉換過後的數字。詳細用法可參考
這兒。
可以得到現成的結果還不錯，不過還是想知道他事實上到底是怎麼換算的，在找了幾篇失敗的解說後，找到了
這篇!! 透過他提供的SQL, 就可以了解其中過程。
Create Or Replace Package B64 Is
B64 Varchar2(64):='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
Function Base64_2Dec(Val Varchar2) Return Number;
Function Dec2_Base64(Val Number) Return Varchar2;
End B64;
/
Create Or Replace Package Body B64 Is
Function Base64_2Dec
(Val Varchar2)
Return Number Is
I Pls_Integer;
J Pls_Integer;
K Pls_Integer:=0;
N Pls_Integer;
V_Out Number(38):=0;
Begin
N:=Length(B64);
For I In Reverse 1..Length(Val) Loop
J:=Instr(B64,Substr(Val,I,1))-1;
If J <0 Then
Raise_Application_Error(-20001,'Invalid Base 64 Number: '||Val);
End If;
V_Out:=V_Out+J*(N**K);
K:=K+1;
End Loop;
Return V_Out;
End;
Function Dec2_Base64
(Val Number)
Return Varchar2 Is
V_In Number;
N Pls_Integer;
V_Out Varchar2(30):='';
Begin
N:=Length(B64);
V_In:=Trunc(Val);
While (V_In>0) Loop
V_Out:=Substr(B64,Mod(V_In,N)+1,1)||V_Out;
V_In:=Trunc(V_In/N);
End Loop;
Return V_Out;
End;
End B64;
/
(有點懶得解釋= = 請自行閱讀，B64那串字就是base-64的64個字元，請看Ref #3)
所以當我下
SQL> select rowid ,
B64.Base64_2Dec(substr(rowid,1,6)) object_no ,
B64.Base64_2Dec(substr(rowid,7,3)) rel_file_id,
B64.Base64_2Dec(substr(rowid,10,6)) block_no,
B64.BASE64_2DEC(SUBSTR(ROWID,16,3)) ROW_NO
from hr.jobs where job_id='XXX';
可以得到
ROWID OBJECT_NO REL_FILE_ID BLOCK_NO ROW_NO
------------------ ---------------- ---------------- -------------- -------------
AAAgwuAAKAAAl7hAAR 134190 10 155361 17
完畢!!
Ref:
- 如何使用base64编码解构Oracle rowid信息
- Oracle DBMS_ROWID
- Base64
Posts
