ORACLE ROWID 分析

ROWID知识补充
Rowid改为：OOOOOOFFFBBBBBBRRR，占用10个字节（32bit data_object_id +10bit rfile# +22bit block +16bit）。其中，O是对象ID，F是文件ID，B是块ID，R是行ID。

Rowid对应值对应10十进制值

A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)

模拟环境

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table t_xff (id number,name varchar2(100));

Table created.

SQL> insert into t_xff values(1,'www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL>  alter table t_xff move;

Table altered.

SQL> select rowid,a.* from t_xff a;

ROWID                      ID NAME
------------------ ---------- ------------------------------
AADye6AAEAAAtCcAAA          1 www.xifenfei.com

相关值计算

Data Object number = AADye6
File               = AAE
Block              = AAAtCc
ROW                = AAA

DATA_OBJECT_ID
AADye6
58*64^0+30*64^1+50*64^2+3*64^3+0*64^4+0*64^5
58+1920+204800+786432+0+0=993210

RFILE#
AAE
4*64^0+0*64^1+0*64^2
4+0+0=4

BLOCK
AAAtCc
28*64^0+2*64^1+45*64^2+0*64^3+0*64^4+0*64^5
28+128+184320+0+0+0=184476

验证结果

SQL> select object_id,data_object_id from dba_objects where object_name='T_XFF';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    993209         993210

SQL> select dbms_rowid.rowid_object('AADye6AAEAAAtCcAAA') data_object_id#,
  2         dbms_rowid.rowid_relative_fno('AADye6AAEAAAtCcAAA') rfile#,
  3         dbms_rowid.rowid_block_number('AADye6AAEAAAtCcAAA') block#,
  4     dbms_rowid.rowid_row_number('AADye6AAEAAAtCcAAA') row# from dual;  

DATA_OBJECT_ID#     RFILE#     BLOCK#       ROW#
--------------- ---------- ---------- ----------
         993210          4     184476          0

dump方式分析

SQL> select rowid,dump(rowid) from t_xff;

ROWID              DUMP(ROWID)
------------------ ------------------------------------------
AADye6AAEAAAtCcAAA Typ=69 Len=10: 0,15,39,186,1,2,208,156,0,0

DATA_OBJECT_ID
0,15,39,186
15*256*256+39*256+186=993210

RFILE#
1,2(取前10位)
000000001 00 =4

BLOCK
2,208,156(取后22位)
000010 11010000 10011100
2^17+208*256+156=184476