Oracle ROWID

最新推荐文章于 2021-07-19 10:18:43 发布

dbaxyc

最新推荐文章于 2021-07-19 10:18:43 发布

阅读量2.8k

点赞数 3

分类专栏： Oracle 索引技术

本文链接：https://blog.csdn.net/laoshangxyc/article/details/8660900

版权

Oracle 索引技术专栏收录该内容

4 篇文章 0 订阅

订阅专栏

Oracle ROWID

ROWID为Oracle表的一个伪列，并不存储在数据库中，只是在执行查询的时候才计算它的值。

注：除了在同一聚簇中可能不唯一外，每条记录的rowid是唯一的

示例：查看rowid列

SQL> select rowid from emp;

ROWID
------------------
AAAR3sAAEAAAACXAAA
AAAR3sAAEAAAACXAAB
AAAR3sAAEAAAACXAAC
AAAR3sAAEAAAACXAAD
AAAR3sAAEAAAACXAAE
AAAR3sAAEAAAACXAAF
AAAR3sAAEAAAACXAAG
AAAR3sAAEAAAACXAAH
AAAR3sAAEAAAACXAAI
AAAR3sAAEAAAACXAAJ
AAAR3sAAEAAAACXAAK
AAAR3sAAEAAAACXAAL
AAAR3sAAEAAAACXAAM
AAAR3sAAEAAAACXAAN
AAAR3sAAEAAAACXAAO

已选择15行。

rowid包含如下内容：

①：对象所在的数据文件号

②：对象所在的块号

③：对象所在行在块内的位置

④：对象号

Oracle正是根据这些内容找到相应的数据的。

可以通过dbms_rowid包把rowid转换成易懂的内容。

示例1：看文件号

SQL> select ROWID,DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)file_id from emp;

ROWID                 FILE_ID
------------------ ----------
AAAR3sAAEAAAACXAAA          4
AAAR3sAAEAAAACXAAB          4
AAAR3sAAEAAAACXAAC          4
AAAR3sAAEAAAACXAAD          4
AAAR3sAAEAAAACXAAE          4
AAAR3sAAEAAAACXAAF          4
AAAR3sAAEAAAACXAAG          4
AAAR3sAAEAAAACXAAH          4
AAAR3sAAEAAAACXAAI          4
AAAR3sAAEAAAACXAAJ          4
AAAR3sAAEAAAACXAAK          4
AAAR3sAAEAAAACXAAL          4
AAAR3sAAEAAAACXAAM          4
AAAR3sAAEAAAACXAAN          4
AAAR3sAAEAAAACXAAO          4

已选择15行。

示例2：看数据块号

SQL> select ROWID,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)block_id from emp;

ROWID                BLOCK_ID
------------------ ----------
AAAR3sAAEAAAACXAAA        151
AAAR3sAAEAAAACXAAB        151
AAAR3sAAEAAAACXAAC        151
AAAR3sAAEAAAACXAAD        151
AAAR3sAAEAAAACXAAE        151
AAAR3sAAEAAAACXAAF        151
AAAR3sAAEAAAACXAAG        151
AAAR3sAAEAAAACXAAH        151
AAAR3sAAEAAAACXAAI        151
AAAR3sAAEAAAACXAAJ        151
AAAR3sAAEAAAACXAAK        151
AAAR3sAAEAAAACXAAL        151
AAAR3sAAEAAAACXAAM        151
AAAR3sAAEAAAACXAAN        151
AAAR3sAAEAAAACXAAO        151

已选择15行。

示例3：看行号

SQL> select ROWID,DBMS_ROWID.ROWID_ROW_NUMBER(rowid)row_number from emp;

ROWID                ROW_NUMBER
------------------ ----------
AAAR3sAAEAAAACXAAA          0
AAAR3sAAEAAAACXAAB          1
AAAR3sAAEAAAACXAAC          2
AAAR3sAAEAAAACXAAD          3
AAAR3sAAEAAAACXAAE          4
AAAR3sAAEAAAACXAAF          5
AAAR3sAAEAAAACXAAG          6
AAAR3sAAEAAAACXAAH          7
AAAR3sAAEAAAACXAAI          8
AAAR3sAAEAAAACXAAJ          9
AAAR3sAAEAAAACXAAK         10
AAAR3sAAEAAAACXAAL         11
AAAR3sAAEAAAACXAAM         12
AAAR3sAAEAAAACXAAN         13
AAAR3sAAEAAAACXAAO         14

已选择15行。

示例4:看对象号

SQL> select ROWID,DBMS_ROWID.ROWID_OBJECT(rowid)object_id from emp;

ROWID               OBJECT_ID
------------------ ----------
AAAR3sAAEAAAACXAAA      73196
AAAR3sAAEAAAACXAAB      73196
AAAR3sAAEAAAACXAAC      73196
AAAR3sAAEAAAACXAAD      73196
AAAR3sAAEAAAACXAAE      73196
AAAR3sAAEAAAACXAAF      73196
AAAR3sAAEAAAACXAAG      73196
AAAR3sAAEAAAACXAAH      73196
AAAR3sAAEAAAACXAAI      73196
AAAR3sAAEAAAACXAAJ      73196
AAAR3sAAEAAAACXAAK      73196
AAAR3sAAEAAAACXAAL      73196
AAAR3sAAEAAAACXAAM      73196
AAAR3sAAEAAAACXAAN      73196
AAAR3sAAEAAAACXAAO      73196

已选择15行。
----验证一下，正确
SQL> col object_name for a30
SQL> select object_id,object_name from user_objects where object_name='EMP';

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     73196 EMP

或用这个更方便


SQL> create or replace function get_rowid
  2  (l_rowid in varchar2)
  3   return varchar2
  4   is
  5   ls_my_rowid     varchar2(200);
  6  rowid_type     number;
  7  object_number     number;
  8  relative_fno     number;
  9  block_number     number;
 10  row_number     number;
 11
 12  begin
 13    dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
 14   ls_my_rowid := 'Object# is      :'||to_char(object_number)||chr(10)||
 15           'Relative_fno is :'||to_char(relative_fno)||chr(10)||
 16           'Block number is :'||to_char(block_number)||chr(10)||
 17           'Row number is   :'||to_char(row_number);
 18    return ls_my_rowid ;
 19   end;
 20
 21  /

函数已创建。

试用一下，不错。

SQL> col content for a30
SQL> select empno,get_rowid(rowid) as content  from emp where empno=7788;

     EMPNO CONTENT
---------- ------------------------------
      7788 Object# is      :73196
           Relative_fno is :4
           Block number is :151
           Row number is   :7

更多参考：

Subprogram	Description
ROWID_BLOCK_NUMBER Function	Returns the block number of a `ROWID`
ROWID_CREATE Function	Creates a `ROWID`, for testing only
ROWID_INFO Procedure	Returns the type and components of a `ROWID`
ROWID_OBJECT Function	Returns the object number of the extended `ROWID`
ROWID_RELATIVE_FNO Function	Returns the file number of a `ROWID`
ROWID_ROW_NUMBER Function	Returns the row number
ROWID_TO_ABSOLUTE_FNO Function	Returns the absolute file number associated with the `ROWID` for a row in a specific table
ROWID_TO_EXTENDED Function	Converts a `ROWID` from restricted format to extended
ROWID_TO_RESTRICTED Function	Converts an extended `ROWID` to restricted format
ROWID_TYPE Function	Returns the `ROWID` type: 0 is restricted, 1 is extended
ROWID_VERIFY Function	Checks if a `ROWID` can be correctly extended by the `ROWID_TO_EXTENDED` function