ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle ROWID

Oracle ROWID

原创 Linux操作系统 作者:tolywang 时间:2005-03-18 00:00:00 0 删除 编辑
我们可能对oracle的rowid的使用并不陌生,不过,如果仔细分析一下,发现其还是有些知识点。

 

 1、rowid是一个伪列,是用来确保表中行的唯一性,它并不能指示出行的物理位置,但可以用来定位行。 2、rowid是存储在索引中的一组既定的值(当行确定后)。我们可以像表中普通的列一样将它选出来。
3、利用rowid是访问表中一行的最快方式。
4、rowid需要10个字节来存储,显示为18位的字符串。
rowid的组成结构为:
data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串),如:AAAADeAABAAAAZSAAA
5、我们可以借助oracle提供的包dbms_rowid,来对rowid进行解析从而获取关于行的相关信息:
bossdb-SQL>select
2 rowid,
3 dbms_rowid.rowid_object(rowid) obj_id,
4 dbms_rowid.rowid_relative_fno(rowid) df#,
5 dbms_rowid.rowid_block_number(rowid) blknum,
6 dbms_rowid.rowid_row_number(rowid) rowno
7 from p_test where rownum<5;
ROWID OBJ_ID DF# BLKNUM ROWNO
------------------ ---------- ---------- ---------- ----------
AAAQ+tAANAAAC6SAAA 69549 13 11922 0 AAAQ+tAANAAAC6SAAB 69549 13 11922 1 AAAQ+tAANAAAC6SAAC 69549 13 11922 2 AAAQ+tAANAAAC6SAAD 69549 13 11922 3
我们可以看到,通过rowid_row_number得到的行号是从0开始的,这是和rownum伪列的一个不同之处。我猜测rowid_row_number在求行号的时候是计算首行的偏移量。

一般来说,当表中的行确定后,rowid就不会发生变化。
但当如下情况发生时,rowid将发生改变:
1、对一个表做表空间的移动后
2、对一个表进行了EXP/IMP后

 

 

 

ROWID

For each row in the database, the ROWID pseudocolumn returns a row's
address. ROWID values contain information necessary to locate a
row:

* which data block in the data file
* which row in the data block (first row is 0)
* which data file (first file is 1)

In most cases, a ROWID value uniquely identifies a row in the
database. However, rows in different tables that are stored

together in the same cluster can have the same ROWID.

Values of the ROWID pseudocolumn have the datatype ROWID.

ROWID values have several important uses:

* They are the fastest means of accessing a single row.
* They can show you how a table's rows are stored.
* They are unique identifiers for rows in a table.

A ROWID does not change during the lifetime of its row. However,

you should not use ROWID as a table's primary key. If you delete
and reinsert a row with the Import and Export utilities, for
example, its ROWID may change. If you delete a row, Oracle may
reassign its ROWID to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE
clauses of a query, these pseudocolumn values are not actually
stored in the database. You cannot insert, update, or delete a

value of the ROWID pseudocolumn.

EXAMPLE:

This statement selects the address of all rows that contain data for
employees in department 20:

SELECT ROWID, ename
FROM emp
WHERE deptno = 20

ROWID ENAME
----------------- ----------
0000000F.0000.0002 SMITH
0000000F.0003.0002 JONES
0000000F.0007.0002 SCOTT

0000000F.000A.0002 ADAMS
0000000F.000C.0002 FORD


-----------------------------------------------------------------------------

DBMS_ROWID包的使用

http://blog.itpub.net/post/3/7835 

DBMS_ROWID是一个比较有用的系统自带的package,主要可以用来处理坏块的问题,于是仔细的研究了一下,这个包可以用来了解fileblockobject idrowid之间的关系,在Oracle8中被引用进来,Oracle7不支持这个包。这个包的定义可以在dbmsutil.sql中找到,在catproc.sql中被调用,并被给予public执行权限。

首先来了解一下这个包中使用的常量:

ROWID类型:

rowid_type_restricted   RESTRICTED - Restricted ROWID

rowid_type_extended     EXTENDED   - Extended ROWID

ROWID验证结果:

rowid_is_valid          VALID   - Valid ROWID

rowid_is_invalid        INVALID - Invalid ROWID

目标类型:

rowid_object_undefined  UNDEFINED - Object Number not defined                                       (for restricted ROWIDs)

ROWID转换类型:

rowid_convert_internal  INTERNAL - convert to/from column of ROWID type

rowid_convert_external  EXTERNAL - convert to/from string format

意外错误:

ROWID_INVALID           invalid rowid format

ROWID_BAD_BLOCK         block is beyond end of file

DBMS_ROWID这个包里面可以使用下面的功能:

function ROWID_CREATE(rowid_type    IN number,

                      object_number IN number,

                      relative_fno  IN number,

                      block_number  IN number,

                      row_number    IN number)

                      return ROWID;      

-- rowid_type      - 类型(restricted=0/extended=1)     

-- object_number   - 对象号     

-- relative_fno    - relative file number     

-- block_number    - 文件包含的block     

-- row_number      - block中的行的行号

下面具体的讨论一下DBMS_ROWID包的用法:

1.  DBMS_ROWID.ROWID_BLOCK_NUMBER:返回一个rowidblock

定义如下:

function dbms_rowid.rowid_block_number

(row_id in rowid)    

return number

SQL> select dbms_rowid.rowid_block_number(rowid) "block" from test;

     block

----------

     23722

2.  DBMS_ROWID.ROWID_CREATE:创建并返回一个基于单独行的rowid,创建的rowid类型是RESTRICTED或者是EXTENDED,这种功能一般都是用于测试目的,因为只有oracle才能创建一个合法的rowid指向数据。

定义如下:

function dbms_rowid.rowid_create

(rowid_type in number    

,object_number in number    

,relative_fno in number    

,block_number in number    

,row_number in number)    

return rowid 

例子:

创建一个restricted rowid

SQL> select dbms_rowid.rowid_create(0, 6877,1,23722,0) from dual;

DBMS_ROWID.ROWID_C

------------------

00005CAA.0000.0001

创建一个extended rowid

SQL> select dbms_rowid.rowid_create(1, 6877,1,23722,0) from dual;

DBMS_ROWID.ROWID_C

------------------

AAABrdAABAAAFyqAAA

3.  DBMS_ROWID.ROWID_INFO: 返回一个单独组件的一个指定的rowid,它只能用于PL/SQL,而不能用于sql语句中。

定义如下:

procedure dbms_rowid.rowid_info     

(rowid_in in rowid     

,rowid_type out number     

,object_number out number     

,relative_fno out number     

,block_number out number     

,row_number out number)
例子:

SQL> set serverout on

SQL> set echo on 

SQL> declare         

  2  my_rowid rowid;         

  3  rowid_type number;         

  4  object_number number;         

  5  relative_fno number;         

  6  block_number number;         

  7  row_number number;         

  8  begin         

  9  my_rowid :=dbms_rowid.rowid_create(1, 6877,1,23722,0);   

 10  dbms_rowid.rowid_info(my_rowid, rowid_type, object_number,

 11  relative_fno, block_number, row_number);         

 12  dbms_output.put_line('ROWID:   ' || my_rowid);         

 13  dbms_output.put_line('Object#:      ' || object_number); 

 14  dbms_output.put_line('RelFile#:     ' || relative_fno);  

 15  dbms_output.put_line('Block#:       ' || block_number);  

 16  dbms_output.put_line('Row#:         ' || row_number);    

 17  end;         

 18  / 

ROWID:   AAABrdAABAAAFyqAAA

Object#:      6877

RelFile#:     1

Block#:       23722

Row#:         0

PL/SQL 过程已成功完成。

4.DBMS_ROWID.ROWID_OBJECT:返回一个rowid的对象号。如果是restricted rowid,则返回0

定义如下:

function dbms_rowid.rowid_object    

(row_id in rowid)    

return number

例子:

SQL> select dbms_rowid.rowid_object(rowid) "OBJECT" from test;

    OBJECT

----------

      6877

SQL> select dbms_rowid.rowid_object(dbms_rowid.rowid_to_restricted(rowid,0)) " OBJECT " from test;

    OBJECT

----------

         0

5.  DBMS_ROWID.ROWID_RELATIVE_FNO: 返回一个rowid的相对文件号。

定义如下:

function dbms_rowid.rowid_relative_fno    

(row_id in rowid)    

return number 

例子:

SQL> select dbms_rowid.rowid_relative_fno(rowid) "relative fno" from test;

relative fno

------------

           1

6.  DBMS_ROWID.ROWID_ROW_NUMBER:返回一个rowid的行号。(从零开始)

定义如下:

function dbms_rowid.rowid_row_number    

(row_id in rowid)    

return number 

例子:

SQL> select dbms_rowid.rowid_row_number(rowid) "row" from test;

       row

----------

         0

7.  DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO:返回一个rowid的完全文件号。

定义如下:

function dbms_rowid.rowid_to_absolute_fno    

(rowid in rowid    

,schema_name in varchar2    

,object_name in varchar2)    

return number 

例子:

SQL> select dbms_rowid.rowid_to_absolute_fno (rowid, 'SYS', 'TEST') "absolute fno" from test;

absolute fno

------------

           1

8.  DBMS_ROWID.ROWID_TO_EXTENDED: 转换一个restricted rowid为一个extended     rowid.如果原始的rowid存储在列中,转换的 就是internal类型;如果原始的rowid是以字符串形式存储的,那转换的就是external类型。

定义如下:

function dbms_rowid.rowid_to_extended    

(old_rowid in rowid    

,schema_name in varchar2    

,object_name in varchar2    

,conversion_type in integer)    

return rowid 

例子:

转换restricted internal rowidextended格式

SQL>select dbms_rowid.rowid_to_extended (dbms_rowid.rowid_to_restricted(rowid,0),'SYS','TEST',0) "extended rowid" from test;

extended rowid

------------------

AAABrdAABAAAFyqAAA

转换restricted external rowidextended格式

SQL> select dbms_rowid.rowid_to_extended ('00005CAA.0000.0001','SYS','TEST',1) from dual;

DBMS_ROWID.ROWID_T

------------------

AAABrdAABAAAFyqAAA

如果参数中的SCHEMAOBJECTnull,则默认是当前的对象

SQL>select dbms_rowid.rowid_to_extended (dbms_rowid.rowid_to_restricted(rowid,0),null,null,0) "extended rowid" from test;

extended rowid

------------------

AAABrdAABAAAFyqAAA

9.  DBMS_ROWID.ROWID_TO_RESTRICTED:转换一个extenededrowid为一个restrictedrowidrestrictedrowid格式为BBBBBBB.RRRR.FFFFF, BBBBBBB代表blockRRRR 代表在block中的行

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84309/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Kill Sessions
下一篇: oracle8的ROWID结构
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13130278