解析ORACLE ROWID

解析ORACLE ROWID
通过rowid查询block，通过block查询rowid

-- 查询rowid 所在数据块 数据行
set line 190 pages 900
col location for a20
col rowid_chr for a30
select rowid,id,dbms_rowid.rowid_object(rowid)||','||dbms_rowid.rowid_relative_fno(rowid)||','||
dbms_rowid.rowid_block_number(rowid)||','||dbms_rowid.rowid_row_number(rowid) location 
,sys.rowid_chr(rowid) rowid_chr
from cog.t2;

-- 查看数据块的rowid
select dbms_rowid.rowid_create(1,101114,6,139,0) rowid_chr from dual;


-- 测试过程   
-- 建表
drop table cog.t2 purge;
create table cog.t2(id int,name varchar2(4000),msg clob) tablespace GZ2;
create unique index cog.ind_t2_u on cog.t2(id);
insert into cog.t2 select rownum,lpad('A',4000,'X'),to_clob(dbms_random.string('x', 4000))||dbms_random.string('x', 4000)
from dual connect by rownum<=10  order by 1;


define owner=COG;
define tb_name=T2;
-- 查询数据DATA_OBJECT_ID
select DATA_OBJECT_ID from dba_objects where owner='&&owner' and OBJECT_NAME='&&tb_name';
DATA_OBJECT_ID
--------------
        101114

-- 查询数据分布 dba_extents
select * from dba_extents where owner='&&owner' and segment_name='&&tb_name';
OWNER  SEGMENT_NAME PARTITION_NAME  SEGMENT_TYPE TABLESPACE  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES BLOCKS RELATIVE_FNO
------ ------------ --------------- ------------ ---------- ---------- ---------- ---------- ---------- ------ ------------
COG    T2                           TABLE        GZ2                 0         26        136      65536      8           26
COG    T2                           TABLE        GZ2                 1         26        168      65536      8           26


-- 注意：前面3个块作为数据文件头，第4个块开始存放数据
-- 函数从数据块反向生成rowid dbms_rowid.rowid_create(type,object_number,relative_fno,block_number,row_number)
select dbms_rowid.rowid_create(1,101114,6,136+3,0) rowid_chr from dual;
ROWID_CHR
------------------------------
AAAYr6AAGAAAACLAAA



-- 查询rowid 所在数据块 数据行
set line 190 pages 900
col location for a20
col rowid_chr for a30
select rowid,id,dbms_rowid.rowid_object(rowid)||','||dbms_rowid.rowid_relative_fno(rowid)||','||
dbms_rowid.rowid_block_number(rowid)||','||dbms_rowid.rowid_row_number(rowid) location 
,sys.rowid_chr(rowid) rowid_chr
from cog.t2;
ROWID                      ID LOCATION
------------------ ---------- --------
AAAYr6AAaAAAACLAAA          4 26,139,0
AAAYr6AAaAAAACMAAA          5 26,140,0
AAAYr6AAaAAAACNAAA          1 26,141,0
AAAYr6AAaAAAACOAAA          2 26,142,0
AAAYr6AAaAAAACPAAA          3 26,143,0
AAAYr6AAaAAAACoAAA          7 26,168,0
AAAYr6AAaAAAACpAAA          8 26,169,0
AAAYr6AAaAAAACrAAA          9 26,171,0
AAAYr6AAaAAAACsAAA         10 26,172,0
AAAYr6AAaAAAACvAAA          6 26,175,0

-- 可以看到，数据存储并不是按照序号顺序存储的, 
-- 但是rowid一定是和块的顺序从小往大排序

-- 测试数据删除插入新的数据
delete from cog.t2 where id in(4,5,1,3,2,7,8);
commit;
alter system checkpoint;
alter system flush shared_pool;
alter system flush BUFFER_CACHE;
-- 再插入2行新的数据
insert into cog.t2 select rownum+50,lpad('A',4000,'X'),to_clob(dbms_random.string('x', 4000))||dbms_random.string('x', 4000)
from dual connect by rownum<=2  order by 1;

ROWID                      ID LOCATION
------------------ ---------- --------------------
AAAYr6AAaAAAACLAAA         52 26,139,0
AAAYr6AAaAAAACqAAA        101 26,170,0
AAAYr6AAaAAAACrAAA          9 26,171,0
AAAYr6AAaAAAACsAAA         10 26,172,0
AAAYr6AAaAAAACtAAA        102 26,173,0
AAAYr6AAaAAAACuAAA         51 26,174,0
AAAYr6AAaAAAACvAAA          6 26,175,0

-- 可以看到，重新插入的数据，使用的ROWID是原来那个块的那个ROWID，ROW不会随主键增加而增加，只会根据当前行(数据存放的块)所在的位置定位，
-- 可以为ROWID数据分片提供理论依据 




-- ROWID为64进制 后3位表示行号 行号理论最大值：262143 实际最大值为65535
ROWID 一共18位 64进制计数A-Za-z0-9(+/)
AAAYr6AAaAAAACLAAA 

AAAYr6 01-06位 data_objectid
AAa    07-09位 data_fileid
AAAACL 10-15位 blockid
AAA    16-18位 rownum

-- 可以用以下SQL来验证rowid计数的64位进制 
select rownum -1 rm,dbms_rowid.rowid_create(1,101114,26,139,0+rownum-1) from dual connect by rownum<=65;

-- 分解rowid
define rowid_chr='AAAYr6AAaAAAACLAAA';
select substr('&rowid_chr',1,6) obj_id,substr('&rowid_chr',7,3) file_id,substr('&rowid_chr',10,6) block_id,substr('&rowid_chr',16,3) rown
 from dual;
-- 查询分解的rowid对应的值
select rowid_tonumber(substr('&rowid_chr',1,6)) obj_id,rowid_tonumber(substr('&rowid_chr',7,3)) file_id
,rowid_tonumber(substr('&rowid_chr',10,6)) block_id,rowid_tonumber(substr('&rowid_chr',16,3)) rown
from dual;

select sys.rowid_tonumber('AAAYr6') from dual;

-- 以下函数可以从ROWID中识别出相应的数值
create or replace function sys.rowid_tonumber(rowid_chr in varchar2) return number as
v_number NUMBER := 0;
begin
with t0 as ( select rownum-1 id  from dual connect by rownum<=64
),t as(
select id,case when id<26 then chr(id+65) when id<52 then chr(id+97-26) when id<62 then chr(id+48-52) when id=62 then '+' when id=63 then '/' else '' end chr
from t0
),p0 as( select lpad(rowid_chr,6,'A') chr from dual 
),p as( select abs(6-id) id,substr(chr,id,1) chr from p0,(select rownum id from dual connect by rownum<=6)
),s as( select t.id,t.chr,p.id,p.chr,t.id*power(64,p.id) loc
 from t join p on t.chr=p.chr
) select sum(loc) into v_number from s;
return v_number;
end rowid_tonumber;
/
select rowid_tonumber('AAa') rowid_tonumber from dual;

create or replace function rowid_chr(rowid_ch in varchar2) return varchar2 as
v_objid NUMBER := 0;
v_fileid NUMBER := 0;
v_blockid NUMBER := 0;
v_rownum NUMBER := 0;
begin
select sys.rowid_tonumber(substr(rowid_ch,1,6)) 
,sys.rowid_tonumber(substr(rowid_ch,7,3)) 
,sys.rowid_tonumber(substr(rowid_ch,10,6)) 
,sys.rowid_tonumber(substr(rowid_ch,16,3)) into v_objid,v_fileid,v_blockid,v_rownum
from dual;
return v_objid||','||v_fileid||','||v_blockid||','||v_rownum;
end rowid_chr;
/
grant execute on rowid_chr to public;
col rowid_chr for a30
select rowid_chr('AAAYr6AAaAAAACLAAA') rowid_chr from dual;
ROWID_CHR
------------------------------
101114,26,139,0