Oracle Rowid 介绍

最新推荐文章于 2023-05-30 13:42:30 发布

曾燕辉

最新推荐文章于 2023-05-30 13:42:30 发布

阅读量355

点赞数

分类专栏： Oracle 数据库

数据库同时被 2 个专栏收录

47 篇文章 0 订阅

订阅专栏

Oracle

45 篇文章 1 订阅

订阅专栏

转载自博客：Oracle Rowid 介绍

一. 官网说明

owid的定义：A globally unique address for a row in a database.

rowid 分为extended rowid 和 restricted rowied.

1.1 Restricted ROWID

Internally, the ROWID is a structure that holds information that the database server needs to access a row. The restricted internal ROWID is 6 bytes on most platforms.

Each restricted rowid includes the following data:

（1）Datafile identifier

（2）Block identifier

（3）Row identifier

The restricted ROWID pseudocolumn is returned to client applications in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID.

1.2 Extended ROWID

The extended ROWID datatype includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. The extended internal ROWID is 10 bytes on most platforms.

Data in an extended ROWID pseudocolumn is returned to the client application in the form of an 18-character string (for example, "AAAA8mAALAAAAQkAAA"), which represents a base 64 encoding of the components of the extended ROWID in a four-piece format, OOOOOOFFFBBBBBBRRR. Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid as well as information specific to extended rowids.

1.3 Rowid Format

Oracle Database uses a rowid to uniquely identify a row. Internally, the rowid is a structure that holds information that the database needs to access a row. A rowid is not physically stored in the database, but is inferred from the file and block on which the data is stored.

An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row. The encoding characters are A-Z, a-z, 0-9, +, and /.

Example 12-1 queries the ROWID pseudocolumn to show the extended rowid of the row in the employees table for employee 100.

Example 12-1 ROWID Pseudocolumn

SQL> SELECT ROWID FROM employees WHERE employee_id = 100;

ROWID

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

AAAPecAAFAAAABSAAA

Figure 12-8 illustrates the format of an extended rowid.

Figure 12-8 ROWID Format

An extended rowid is displayed in a four-piece format, OOOOOOFFFBBBBBBRRR, with the format divided into the following components:

（1）OOOOOO

The data object number identifies the segment (data object AAAPec in Example 12-1). A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.

（2）FFF

The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).

（3）BBBBBB

The data block number identifies the block that contains the row (block AAAABS in Example 12-1). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace.

（4）RRR

The row number identifies the row in the block (row AAA in Example 12-1).

After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example,

（1）if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.

（2）If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.

-- rowid 改变的条件

Note:

Internally, the database performs row movement as if the row were physically deleted and reinserted. However, row movement is considered an update, which has implications for triggers.

二. rowid 说明

rowid是伪列（pseudocolumn），伪劣的意思是实际上这一列本身在数据字典中并不存在，在查询结果输出时它被构造出来的。

rowid并不会真正存在于表的data block中,但是他会存在于index当中，用来通过rowid来寻找表中的行数据。

2.1 利用rowid来得到相关信息

SQL> conn sys/admin as sysdba

已连接。

SQL> create table bl(id number);

表已创建。

SQL> insert into bl values(1);

已创建 1 行。

SQL> select owner,segment_name,file_id,RELATIVE_FNO,block_id from dba_extents where owner='SYS' and segment_name='BL';

OWNER SEGMENT_NA FILE_ID RELATIVE_FNO BLOCK_ID

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

SYS BL 1 1 62129

SQL> desc dbms_rowid.rowid_info

PACKAGE SYS.DBMS_ROWID

PROCEDURE ROWID_INFO

Argument Name Type In/Out

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

ROWID_IN ROWID IN

ROWID_TYPE NUMBER OUT

OBJECT_NUMBER NUMBER OUT

RELATIVE_FNO NUMBER OUT

BLOCK_NUMBER NUMBER OUT

ROW_NUMBER NUMBER OUT

TS_TYPE_IN VARCHAR2 IN

SQL> set serveroutput on

SQL>DECLARE

v_rowid_type NUMBER;

v_OBJECT_NUMBER NUMBER;

v_RELATIVE_FNO NUMBER;

v_BLOCK_NUMBERE_FNO NUMBER;

v_ROW_NUMBER NUMBER;

BEGIN

DBMS_ROWID.rowid_info (

rowid_in => 'AAAJVnAANAAAACiAAA',

rowid_type => v_rowid_type,

object_number => v_OBJECT_NUMBER,

relative_fno => v_RELATIVE_FNO,

block_number => v_BLOCK_NUMBERE_FNO,

ROW_NUMBER => v_ROW_NUMBER);

DBMS_OUTPUT.put_line ('ROWID_TYPE: ' || TO_CHAR (v_rowid_type));

DBMS_OUTPUT.put_line ('OBJECT_NUMBER: ' || TO_CHAR (v_OBJECT_NUMBER));

DBMS_OUTPUT.put_line ('RELATIVE_FNO: ' || TO_CHAR (v_RELATIVE_FNO));

DBMS_OUTPUT.put_line ('BLOCK_NUMBER: ' || TO_CHAR (v_BLOCK_NUMBERE_FNO));

DBMS_OUTPUT.put_line ('ROW_NUMBER: ' || TO_CHAR (v_ROW_NUMBER));

END;

结果：

ROWID_TYPE: 1

OBJECT_NUMBER: 38247

RELATIVE_FNO: 13

BLOCK_NUMBER: 162

ROW_NUMBER: 0

2.2 . Rowid的结构

ROWID 格式:

扩展的ROWID 在磁盘上需要10 个字节的存储空间，并使用18 个字符来显示。

它包含下列组成元素：

1. 数据对象编号：每个数据对象（如表或索引）在创建时都分配有此编号，并且此编号在数据库中是唯一的

2. 相关文件编号：此编号对于表空间中的每个数据文件是唯一的

3. 块编号：表示包含此行的块在数据文件中的位置

4. 行编号：标识块头中行目录位置的位置

在内部，存储的10个字节(bytes)，即80位(bit)又按如下规则进行划分：

（1）数据对象编号需要32 bit

（2）相关文件编号需要10 bit

（3）块编号需要22 bit

（4）行编号需要16 bit

在oracle 8以前，一个rowid占用6个字节大小的存储空间(10bit file#+22bit block#+16bit row#), rowid格式为:BBBBBBBB.RRRR.FFFF。

在oracle 8以后， rowid的存储空间扩大到了10个字节(32bit object#+10bit rfile#+22bit block#+16bit row#),文件号仍然用10位表示,只是不再需要置换,为了向后兼容,同时引入了相对文件号(rfile#),所以从Oracle7到Oracle8,Rowid仍然无需发生变化.

Rdba(Tablespace relative database block address)就是rowid中的rfile#+block#.

rowid这样改变之后，数据库中数据库文件个数的限制从整个数据库最多只能有的2^10-2=1022个数据文件(去掉全0和全1), 变为了每个表空间中可以最多有2^10-2个数据文件。

所以说，数据库能支持的数据文件最大数是受rowid的长度限制的。

需要注意的是: local index中存储的rowid是6个字节，而global index中存储的rowid是10个字节。

那么增加的32bit object# 这个前缀主要就是用来定位表空间的，同时这个object#其实对应的就是data_object_id，由于一个段对象只能属于一个表空间，同时data_object_id就是标识了一个段的物理存储id.因此object#+rfile#就可以唯一定位当前的rowid是在那个数据文件上了。

可以通过dbms_rowid这个包来转换我们的rowid成不同组成部分：

       dbms_rowid.rowid_object(rowid)---> 32bit object#
       dbms_rowid.rowid_relative_fno(rowid)---> 10bit rfile#
       dbms_rowid.rowid_block_number(rowid)---> 22bit block#
       dbms_rowid.rowid_row_number(rowid)---> 16bit row#

扩展的ROWID 使用以64 为基数的编码方案来显示，该方案将六个位置用于数据对象编号、三个位置用于相关文件编号、六个位置用于块编号、三个位置用于行编号。

以64 为基数的编码方案使用字符“A-Z”、“a-z”、“0-9” 和“/”。共有64 个字符，如下例所示：