ORACLE ROWID 分析

最新推荐文章于 2024-09-11 18:29:30 发布

蜷缩的蜗牛

最新推荐文章于 2024-09-11 18:29:30 发布

阅读量710

点赞数

本文深入解析了Oracle数据库中ROWID的概念，包括其组成部分、如何计算及应用实例，通过SQL操作展示了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
       
        本文转自链接：http://www.xifenfei.com/2893.html，感谢作者惜分飞