oracle rowid詳解

??? ? 今天是2013-09-15，存儲在數據庫中的每一行數據都有一個地址，oracle使用rowid數據類型在存儲地址。rowid有如下類別：
1）physical rowids，存儲堆表、簇表以及表和索引分區中每行地址
2）logical rowids，存儲索引組織表的每行的地址
3）foreign rowids，定義在外部表中。如通過gateway訪問db2表，這不是標準數據rowids

在oracle數據庫中有一個偽劣的叫rowid，這個偽劣像一個表中的字段一樣，但是不存在表中。只能查詢偽劣，但是不能夠
insert、delete他們的值。
參考： http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT88811
eg：
SQL> select rowid,grade,losal,hisal from amy;

ROWID?????????????????? GRADE????? LOSAL????? HISAL
------------------ ---------- ---------- ----------
AAAVw1AAHAAAACTAAA????????? 1??????? 700?????? 1200
AAAVw1AAHAAAACTAAB????????? 2?????? 1201?????? 1400
AAAVw1AAHAAAACTAAC????????? 3?????? 1401?????? 2000
AAAVw1AAHAAAACTAAD????????? 4?????? 2001?????? 3000
AAAVw1AAHAAAACTAAE????????? 5?????? 3001?????? 9999

SQL>

二）rowid 結構：
oracle數據庫使用rowid在數據庫中唯一的定義一行。在內部，這個rowid的結構信息保留了數據庫可以訪問的那一行數據。
Note:

Beginning with Oracle8, Oracle SQL incorporated an extended format for rowids to efficiently support partitioned tables and indexes and tablespace-relative data block addresses without ambiguity.

查看一個網友的說法如下，挺合理的。

rowid就是唯一標志記錄物理位置的一個id，在oracle 8版本以前，rowid由file# block# row#組成，占用6個bytes的空間，10 bit 的 file# ，22bit 的 block# ，16 bit 的 row#。

從oracle 8開始rowid變成了extend rowid，由data_object_id# rfile# block# row#組成，占用10個bytes的空間， 32bit的 data_object_id#,10 bit 的 rfile#,22bit 的 block#,16 bit 的 row#.由于rowid的組成從file#變成了rfile#，所以數據文件數的限制也從整個庫不能超過1023個變成了每個 表空間 不能超過1023個 數據文件。
說了rowid的組成，那么我們再來看看rowid在索引里面占用的字節數又是什么樣子的。在oracle 8以前索引中存儲的rowid占用字節數也是6bytes,在oracle8之后，雖然oracle使用了extend rowid，但是在普通索引里面依然存儲了bytes的rowid，只有在global index中存儲的是10bytes的extend rowid，而extend rowid也是global index出現的一個必要條件，下面我們會解釋原因。

為什么golbal index需要把data_object_id#也包含在index rowid entry中呢？如果不包含會這么樣？首先我們需要知道index的rowid entry的存在是為了能根據它找到表的這條記錄存在哪個具體的物理位置，我們需要知道它在哪個數據文件，在哪個block，在那一行，普通的索引 oracle根據rfile#,block#,row#就可以知道了，但是partition table可以分布在多個 表空間 ，也就是可以分布在多個數據文件，當我們建立local index時，index rowid entry并不包含data_object_id#，因為oracle可以知道這個index對應的是哪一個table分區，并可以得到table分區的 ts#(tablespace號)，那么oracle根據ts#和rfile#就可以找到具體的數據文件。但是如果換成是golbal index，如果不包含data_object_id#，那么我們并不能知道這個索引對應著哪個表分區，也自然不能知道它的rfile#和file#的轉 換關系，所以它將找不到所對應的記錄。包含data_object_id#后,oracle可以根據data_object_id#實現rfile#和 file#的轉換然后找到記錄對應的物理位置。

一個擴展的rowid保護數據對象號，使用64編碼指定每行數據的物理地址。這64個編碼為：A-Z,a-z,0-9,+,and /.

32bit的 data_object_id#（AAAPec）,10 bit 的 rfile#（AAF),22bit 的 block#（AAABS),16 bit 的 row#.（AAA）

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

• 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.

• FFF

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

• 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.

• 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, if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on. If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.

三）rowid data type

???? Rowids contain the following information:

• The data block of the data file containing the row. The length of this string depends on your operating system.

• The row in the data block.

• The database file containing the row. The first data file has the number 1. The length of this string depends on your operating system.

• The data object number , which is an identification number assigned to every database segment. You can retrieve the data object number from the data dictionary views USER_OBJECTS , DBA_OBJECTS , and ALL_OBJECTS . Objects that share the same segment (clustered tables in the same cluster, for example) have the same object number.

Rowids are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, and the plus sign (+) and forward slash (/). Rowids are not available directly. You can use the supplied package DBMS_ROWID to interpret rowid contents. The package functions extract and provide information on the four rowid elements listed above.

另外注意索引組織表與外部表的urowid；

UROWID Data Type

???? The rows of some tables have addresses that are not physical or permanent or were not generated by Oracle Database. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Rowids of foreign tables (such as DB2 tables accessed through a gateway) are not standard Oracle rowids.

???? Oracle uses universal rowids ( urowids ) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids. Both types of urowid are stored in the ROWID pseudocolumn (as are the physical rowids of heap-organized tables).

Oracle creates logical rowids based on the primary key of the table. The logical rowids do not change as long as the primary key does not change. The ROWID pseudocolumn of an index-organized table has a data type of UROWID . You can access this pseudocolumn as you would the ROWID pseudocolumn of a heap-organized table (using a SELECT ... ROWID statement). If you want to store the rowids of an index-organized table, then you can define a column of type UROWID for the table and retrieve the value of the ROWID pseudocolumn into that column.

四）dbms_rowid

The DBMS_ROWID package lets you create ROWIDs and obtain information about ROWID s from PL/SQL programs and SQL statements. You can find the data block number, the object number, and other ROWID components without writing code to interpret the base-64 character external ROWID . DBMS_ROWID is intended for upgrading from Oracle database version 7 to Oracle database version 8.X.

然后看看這個包的幾個function

如下：

1、rowid block number function：查看rowid的數據塊號

2、rowid create function：為 了測試創建一個rowid列

3、rowid info procedure ：查看rowid中包含的類型

4、rowid object function：然后擴展rowid的對象號

5、rowid relative fno funciton：返回rowid的文件號

6、rowid row number function：返回行號

7、rowid to absolute fno function：在指定表中返回與rowid相關的絕對文件號

8、rowid to extended function：從受限的rowid轉為擴展rowid

9、rowid to restricted function:從擴展rowid轉為受限rowid

10、rowid type function：返回rowid的類型，1為擴展rowid，0為受限rowid

11、rowid verify function：驗證從rowid_to_extended函數轉換來的rowid是否正確：

eg：

我想知道amy這張表中每行數據位于哪個數據文件，那個數據塊，行號是多少；

SQL> select dbms_rowid.rowid_object(rowid) as object,dbms_rowid.rowid_relative_fno(rowid) as filenum,
? 2? dbms_rowid.rowid_block_number(rowid) as block,dbms_rowid.rowid_row_number(rowid) as rown,grade,losal,hisal from amy;

??? OBJECT??? FILENUM????? BLOCK?????? ROWN????? GRADE????? LOSAL????? HISAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
???? 89141????????? 7??????? 147????????? 0????????? 1??????? 700?????? 1200
???? 89141????????? 7??????? 147????????? 1????????? 2?????? 1201?????? 1400
???? 89141????????? 7??????? 147????????? 2????????? 3?????? 1401?????? 2000
???? 89141????????? 7??????? 147????????? 3????????? 4?????? 2001?????? 3000
???? 89141????????? 7??????? 147????????? 4????????? 5?????? 3001?????? 9999

SQL>

SQL> select object_name,object_type,created from user_objects where object_id in (select dbms_rowid.rowid_object(rowid) from amy);

OBJECT_NAME????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? OBJECT_TYPE???????? CREATED
-------------------------------------------------------------------------------------------------------------------------------- ------------------- ---------
AMY????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? TABLE?????????????? 15-SEP-13

SQL>

這是我們經常可能需要的信息：

另外還有一個rowid_info procedure

先看一下該 過程的源碼：

如下：“

? -- ROWID_INFO breaks ROWID into its components and returns them:
? --
? -- rowid_in - ROWID to be interpreted
? -- rowid_type - type (restricted/extended)
? -- object_number - data object number (rowid_object_undefined for restricted)
? -- relative_fno - relative file number
? -- block_number - block number in this file
? -- file_number - file number in this block
? -- ts_type_in - type of tablespace which this row belongs to
? --????????????? 'BIGFILE' indicates Bigfile Tablespace
? --????????????? 'SMALLFILE' indicates Smallfile (traditional pre-10i) TS.
? --????????????? NOTE: These two are the only allowed values for this param
? --
? procedure 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,
??????????????????????? ts_type_in IN varchar2 default 'SMALLFILE');
? pragma RESTRICT_REFERENCES(rowid_info,WNDS,RNDS,WNPS,RNPS);

好了，那么我想使用該procedure查看amy中的grade為1的相關信息如下：

SQL>? declare
? 2?? amy_rowid_type number;
?amy_object_number number;
? 3??? 4?? amy_relative_fno number;
? 5?? amy_block_number number;
?amy_row_number number;
? 6??? 7?? begin
? 8?? dbms_rowid.rowid_info(
? 9?? rowid_in=>'AAAVw1AAHAAAACTAAA',
?10?? rowid_type=>amy_rowid_type,
?11?? object_number=>amy_object_number,
?12?? relative_fno=>amy_relative_fno,
?13?? block_number=>amy_block_number,
?14?? row_number=>amy_row_number);
?15?? dbms_output.put_line('rowid_type: '||amy_rowid_type);
?16?? dbms_output.put_line('object_number: '||amy_object_number);
?17?? dbms_output.put_line('relative_fno: '||amy_relative_fno);
?18?? dbms_output.put_line('block_number: '||amy_block_number);
?19?? dbms_output.put_line('row_number: '||amy_row_number);
?20?? dbms_output.put_line('The name is:'||'amy');
?21?? end ;
?22? /
rowid_type: 1
object_number: 89141
relative_fno: 7
block_number: 147
row_number: 0
The name is:amy

PL/SQL procedure successfully completed.

SQL>

?

?

oracle rowid 詳解